Database Design and Reference Tables
-
GENERIC! This alone should be a warning. What does generic mean? A replacement or different way of doing something and that something is usually at the expense of design and function. My Dad had two sayings he drilled into his kids, “You get what you pay for!" and “If you have time to do something, then take the time to do it right!” I am sure Dad would say to me (if he could from above) “Generic tables: Remember you get what you pay for. So you must not have time to do it right?” Generic reference tables for group of items very similar in nature might be okay where there's a code followed by the meaning, but with no repeating numeric values. Additionally, its contents would never change. But I cannot come up with an example. (I was thinking States and Countries, but the names keep changing in places like Africa).
Just like drugs, generics don't work as well as the originals. I believe I took this generic approach in 1972 when I first started programming -- assembly language on a UNIVAC 1005, but changed a year later using FORTRAN and COBOL on a Honeywell 6000. Sorry generic don't work for reference tables just as those substitutes for that purple pill.
-
Look in the dictionary, and you might find "bad design" in the definition of generic. Is it any wonder with people still designing garbage after having good design principles for so many years, that this kind of garbage design still pops up? People seem to never learn. They always have a newer, better way. Guess what, it has been done before. And that is why we have the design principles for normalizing a database that we have now. IT DID NOT WORK!!!
-
My question to Steve is Why did he repost a bad article with clearly misleading information. I would take it off the server.
-
Read it again... from a position where you need to create a bad design and that articles starts to make sens (which is what the writter intended iirc).
EDIT
I was reffering to this article which had about the same kind of response
: http://www.sqlservercentral.com/columnists/dpeterson/lookuptablemadness.asp
-
Rarely have I disagreed more with an article I've read on here.
Since when do you have to use a generic lookup table to have an integer key??? Your state lookup table should be an integer value as the primary key and then you could have a state abbreviation column and a state name column.
State Lookup Table StateID StateAbbreviation StateName 1 AL Alabama 2 CA California 3 TX Texas 4 FL Florida Also, by forcing the extra dependency on a string column being required as a unique identifier for your lookups, every query that performs a lookup has an extra performance hit on it. Your primary key would have to be a composite key of intID and strCategory, which judging by your example table is clustered on strCategory first and intID second. Your PK index is now a huge index and index seeks are going to take a much longer time. And since the clustered index is huge, any subsequent non-clustered indexes on the table will be considerably bigger and slower as well.
My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url] -
Amen.
Hey this is the first time I post the 100th message on a thread
. -
Sounds like the new Access engine will help the guys out that want a poor design...
-
If there isn't a policy to stop 'really bad' articles being published on SQLServerCentral, there should at least be one that stops said articles appearing in the "One year ago..." section of the newsletter. Re-promoting these articles gives credence where absolutely none is due!
-
For that the articles would have to be flagged in some way and/or the site-owners would have to read the comments & response posts to every single article..kinda monumental..that task...
You could always post this in the "Suggestions" forum...I know for a fact that all suggestions are considered very seriously by Steve etc..
**ASCII stupid question, get a stupid ANSI !!!** -
IMO having one physical table representing multiple logical tables is extremely poor design - I'm surprised a normally reputable/conservative site such as SQL Server Central would promote this idea
Having one physical table per logical lookup table has benefits as follows:
- Easier to reverse-engineer a data model from a physical database
- Improved performance as database page hits are more likely to be on discrete pages for disparate tables (tiny as little tables like these are probably cached but every little bit helps)
- Lends itself to minor divergence e.g. a specific lookup table has an extra column other than code/description pairs
- Allows tables to have names that represent their function
I've seen all 3 variants on this:
1. 1 physical table per logical table
2. 1 physical table for ALL logical tables but named views representing each logical table
3. I physical table for ALL logical tables and you just have to know how to code it
In my opinion these variants are listed above in decreasing order or desirability
-
Generic lookup tables may seem to be good solutions at first. It leads to complexity, rigidity of design, and lack of referential integrity to name a few. Please read this article http://www.sqlservercentral.com/articles/Advanced/lookuptablemadness/1464/#
-
Glad to hear that other people thought this idea was horrible.
Viewing 12 posts - 91 through 101 (of 101 total)
You must be logged in to reply to this topic. Login to reply