ooak keys
-
Could someone comment on the wisdom of using an "ooak" as part of 'all' / 'most' database table keys and foreign keys...
Thanks,
Paul
-
What's an "ooak"?
-
http://en.wikipedia.org/wiki/OOAK - One Of A Kind - i.e., uniqueness
As a general rule, in databases, you can either be REQUIRED to promise OOAK, or you can only have it recommended. But if it's a recommended case, then it's not really a key, as most databases think of it.
OK gang, have at me. I'm in a provocative (but not combative) mood.
-
If you mean, should a table always have a primary key, then the answer is yes.
A table must have a primary key to be in first normal form.
-
I am not debating the Primary Key issue - it's the use of an "ooak" in every (over naturalized database) table and using the ooak's as 'the' relationship to children tables(foreign keys)...
p.s. the ooak is generated for new table entries and is 16-bytes in length and is part of the database key...
-
Okay, you are talking about a synthetic key; one that has no actual relation to the data being stored in the record. The answer comes down to this, it depends.
What are you attempting to do or accomplish. In a normalized OLTP system, I would say no. In a data warehouse or data archival environment then I would say yes.
The key item in either, is that once assigned, the key shouldn't change. I worked at a company where the softer system used a "name" field as the primary key instead of the internally generated number. This was "fun" when a vendor decided to rename their products.
-
creating un-needed keys or parts of keys, presumably for 16 bytes we're talking guid's, will increase the width of keys and indexes for no purpose, increase storage size and degrade performance. for a small database you might not notice this but see what effect this has on a 400 million row table.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
Yes - the ooak (one-of-a-kind) number is a guid - - - I contend it is very wasteful and suggested an dumb number integer instead of the expensive 16-character guid...
-
The big question that has not been answered, is what type of application is going to use the database. Again, using a ooak or synthetic id needs to be balanced against the application and its requirements.
-
It's in a design for a Membership Type system that keeps up with members, dues, attendance, progress, payment and attendance history, etc... Virtually every table in the database has a PrimaryKey that is a guid.
I also feel the database is very over normalized - (like the ZipCode is data in a guid-PrimaryKey table) then the data records all carry the guid and have to join for the Zip) -but- I guess Normalization is another topic and would always start a brisk discussion.
-
in general guids were one of the worst added features for sql server that allowed people to misuse. Most database's don't need guid's and at 25% the size an int would be much better. Your zip is a good example where the key is probably wider than the data.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
Hi,
As a matter of interest what is the interface for this database and is this database involved in any form of replication?
K.
-
Having zip codes in a separate table is going to be a maintenance nightmare. In areas of high population growth, the USPS is continually splitting zip code areas and creating new zip codes. IMHO you should not store data in a lookup table when that data is outside your control. The USPS offers the WebTools API web service that allows you to scrub addresses & look up the zip+4 which would have been a much smarter solution. MCTW
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply