If there is a concern that the number of lookup tables will exponentially grow and you wont know where anything is....I guess that is a different topic.
And hence forth, you know why I created this...
Joe Celko (10/19/2007)
Let me be brutal about this. I can see one -- and only ONE -- thing you have done right in this non-relational schema. This thing is so bad I want to use it in a book on bad SQL.
Wow guys, pretty brutal. No constructive criticism? I used to have respect for you but obviously you just like to hear yourself talk. Apparently you think I am a naive database coder or something.
Yes, I've read your article before and it basically has no merit. I've read ALL the pros and cons on this design! You made the whole thing way too complicated plus your little example table design was completely flawed from the beginning. No one in their right mind would use the design you mentioned. For one there's no Primary Key on it. I'm sure you didn't notice because you were too busy trying to think up witty quips, but I store the Primary Key of the values, not some Magical number.
I only use these "reference" tables for simple lookup values (IDs & Descriptions). When the Lookup "Type" (i.e. AddressTypes) becomes too complex or merits additional schema, that is when I offload it into it's own table. Removing the current Foreign Key and a couple of update statements later, I can reapply the new F-Key to the new stand-alone lookup table. So, flexibility and future growth are accounted for.
There are two sides to designing. The Logical design says "Hey we need 600 lookup tables!". The Physical design says "Umm, they're all the same schema and they only store IDs and Descriptions". It doesn't make sense to me to store 30 tables with an ID and Description. To me, designing databases is about storing the most data with the least bit of space. Even if the single lookup values table had many values, I've never seen mine grow above 500 rows. Too little for indexing to be effective but all the same I do set some indexes. It's also just right for pinning in memory. If this design becomes too large, then it's time to reevaluate and re-read the 1st paragraph. Just because you do not understand the design doesn't make it bad.
You have no relational keys. Didn't you know that proprietary auto-numbering is NEVER a relational key by definition? All you have is an exposed physical locator, as if you were creating an index into a sequential file.
What are you yacking about "Proprietary numbering"? I didn't realize the IDENTITY function was no longer used!? In case you don't understand, the Constant column is ONLY used when necessary for the values. Taking AddressTypes for example, maybe one row's Constant would be 'MAIN' and another 'MAILING'. The ID should never matter to a programmer, T-SQL coder or anyone. Constants do not change but IDs may.
I've used this design for about 3 years now and never had a problem with it. I was able to consolidate over 30 "lookup" tables. Yes, there are Foreign Keys. Every table that uses this as a Lookup HAS a Foreign Key pointing back to the Value ID. Obviously one flaw of this is that it's possible that one ID from another List ends up in your column, but how often have I had it beat into my head that "Business logic doesn't exist in the database". So, if you're that stupid of a programmer to give a user a drop-down of ALL the values instead of just those that belong in that column, then shame on you.
Did you know that keeping audit data in a table is illegal? And that if it were not illegal, it is stupidly dangerous. Do you also keep the log file on the same hard disk as the DB? DUH!
Also, what crack pipe are you smoking, who mentioned anything about auditing? I manage all my auditing through a Service Broker which hands off the audit data to a separate database asynchronously so it doesn't block inserts / updates. The modified dates are used to ensure there are no concurrency conflicts when two users are attempting to update the same row. The trigger is in place in case some stupid programmer creates a Proc that doesn't specify the ModifiedDate.
Do you have any idea why the BIT and BIT VARYING data types were removed from SQL? They are assembly language and are a sure sign of design flaws.
Oops, you got me on one thing! A Nullable Bit field! Overlooked on my part. However, bits are very useful and should not be counted out. I guess if you were king then all FLAGS in every programming language would be removed as well? What happens when I need to store multiple Statuses for my Application Members (ex. LockedOut but Active)? I suppose you'd tell me to add another bit column? And when that changes to another status that needs to be combined into the first two? Another column?
Since the rest of the schema is soooo screwed up, you are trying to patch it with procedural code and you did it in the worst way. Use a trigger to replace a DEFAULT???
Apparently you have no idea what the trigger is actually doing. AND Default values are only inserted for new records. If you didn't notice, the Trigger is AFTER UPDATE. Again, you love to talk before you read.
I didn't just fall off the boat yesterday and I didn't wake up one day and think this would be a great idea. I've proven it's usefulness time and again over many years and I will continue to use it.
I wonder, since this design is so non-relational in your book, how I would love to see your proposed solution to a GIS database.