Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
drsloat
Starting Member
45 Posts |
 Posted - 2005-08-24 : 13:20:01
|
| I have a table that stores information about urls. It has 3 columns (domain, relativepath, and filename) that represent the url. The whole url should be the primary key for the table, but sometimes there is no filename.Is it a good idea to store empty strings in the column for those cases, so that i can make the field Non nullable and part of the primary key?Could I make a calculated column that concatenates the 3 fields (substituting and empty string when null) and make this the primary key? |
|
|
drsloat
Starting Member
45 Posts |
Posted - 2005-08-24 : 13:39:55
|
| So, i think the best option is to make a surrogate primary key (call it urlID) and then i can put a unique constraint on the 3 columns, which will let me make filename nullable. |
 |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
Xerxes
Aged Yak Warrior
666 Posts |
Posted - 2005-08-24 : 14:18:31
|
quote:
Originally posted by X002548
That's what I would doBrett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Really? That's what you would do?Semper fi, XERXES, USMC(Ret.)------------------------------------------------------The Marine Corps taught me everything but SQL! |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2005-08-24 : 14:45:03
|
quote:
Originally posted by Xerxes
quote:
Originally posted by X002548
That's what I would doBrett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Really? That's what you would do?Semper fi, XERXES, USMC(Ret.)------------------------------------------------------The Marine Corps taught me everything but SQL!
Yup.What would you do?Have a PK that's 500 bytes wide?Brett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2005-08-24 : 18:53:56
|
I reckon get rid of the NULL, it only adds more work.For arguments sake let's say this represents the logical schema.URL (DomainName VARCHAR(255), RelativePath VARCHAR(255) , FileName VARCHAR(255) KEY(DomainName, RelativePath, FileName))If we try and directly implement it with a clustered PK (no nulls)CLUSTERED PRIMARY KEY (DomainName, RelativePath, FileName) That seems simple to me...Now the surrogate option (nulls allowed)..CLUSTERED PRIMARY KEY (UrlID)UNIQUE CONSTRAINT (DomainName, RelativePath, FileName)CONSTRAINT LEN(FileName) > 0 OR FileName IS NULL Not so simple..Either way you will have to manage large index keys (large clustered vs small clustered and a large non-clustered).Unless the surrogate is going to be used as a FK somewhere, it seems utterly pointless.DavidMA front-end is something that tries to violate a back-end. |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2005-08-25 : 12:35:23
|
Domain could be a separate table, maybe You want to store info about the domains.But I can see the way that requirement would be solved in the current approach : (just make relativepath NULL)  Anyway UrlId or no UrlId, don't make the filename column nullable, there is no point in doing that as I see it.rockmoose |
|
|
|
|
|
|
|
|
Primary Key Design Question