Database Design and Reference Tables
-
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/rnarasimhan/databasedesignandreferencetables.asp
-
For a warning about taking this approach to extremes, see Don Peterson's article on Lookup Table Madness...
http://www.sqlservercentral.com/columnists/dpeterson/lookuptablemadness.asp
If it ain't broke, don't fix it...
-
The Article is interesting, but to be honest I am against Generic Lookup Tables ... I don't think it is good database design at all.
Here is an article that describes my point of view on Generic Lookup Tables written by Don Peterson back in 2004:
http://www.sqlservercentral.com/columnists/dpeterson/lookuptablemadness.asp
"1.We can store integer values to most of the data and have the description stored in the "GenericLookUp" table. As we know that performance is always going to be better when we deal with numeric columns than non numeric columns."
You can store integer Values as keys for the 'reference tables'
2.Easy maintainabilty. Lets say we have 20 LookUp tables in a application. With this "GenericLookUp" table, we just need 3 stored pros(Insert/Update/Delete) to maintain the data.
What about Integrity? I find it easier to maintain 20 well specified tables than 1 generic table.
3.Centralized information in one place. This table would be almost like a data dictionary.
MUCK tables do not agree with Normalization. And where do you stop? You might just as well put everything in one big generic table, (irony): it would save having to do joins ...
nano
-
~nano
I'm glad you got into this one first - completely contradicts the madness article a few months ago. Having been down both tracks (as have we all) I'm still in 2 minds as to the "best" method.
I love the integrity of multiple tables, I hate the job of doing all the GUI work to maintain each one. Yeah, I know you can do a generic form but then you get the odd tweak needed to support a business rule and 3 months later you have a monster. Still I've got the process down to where I can do a complete table/procs/GUI in less than 15 minutes but it's all rote
.MAF
-
I agree with the previous comments.
I used generic lookup tables in one project but then soon started running into some of the problems refferred to in http://www.sqlservercentral.com/columnists/dpeterson/lookuptablemadness.asp
Some of those entities started to require more than a Description column, they wanted things like Code and ShortName, which meant having lots of nulls and was certainly breaking normalisation.
However, I don't see anything wrong with having a generic middle tier data object (e.g. .NET strongly typed datset) which has two columns, say Id and Description which can be used to read any look up table into. This can allow for generic ways in handling populating drop down lists and caching reference tables.
-
I agree with the other posters, that this is a great example of poor database design...
A few points to back this up:
1) The queries needed to pull data from this 'generic' table match based on a string value; this is far slower than using an int.
2) If, as the author suggests, a typical db has 20-50 lookup tables, this single-table approach will soon get extremely cumbersome and slow... Imagine how many records will be in this table, and how often it will be hit.
3) Related to point 1), joins to this table will also be slow, as the primary key is likely to be a composite.
4) Jst nitpicking here, but what kind of silly naming convention for the column names (int, str, etc...) is that? It may be good practice in regular programming, but in a Db this is totally unneccessary.
There are several other issues, but I will limit myself, as this idea was clearly not properly thought out....
Regards,
David :o)
-
In the distant past I had a time when I thought it would be very clever to have a generic lookup table as well. This was in 1988 using XDB SQL on an 80286 server, running at 16MHz which was very fast for an 80286. Oracle, Ingres, DB2 and Sybase were all mainframe products then, and MS SQL Server hadn't been invented yet.
But that was before I had learnt (1) proper normalisation techniques and (2) the reasons for using referencial integrity and foreign key constraints. This generic style makes it more difficult to maintain referencial integrity.
-
My new database design:
Create table TheUltimateGenericSolution(
Key int not null identity primary key,
Stuff text) -- Put in what you like in XML format
We don’t need any other tables - Now that’s generic!
As ridiculous as my solution is, strangely it benefits from all the advantages mentioned by the guy who wrote this article:
1.We can store integer values to most of the data and have the description stored in the "GenericLookUp" table. As we know that performance is always going to be better when we deal with numeric columns than non numeric columns.
2.Easy maintainabilty. Lets say we have 20 LookUp tables in a application. With this "GenericLookUp" table, we just need 3 stored pros(Insert/Update/Delete) to maintain the data.
3.Centralized information in one place. This table would be almost like a data dictionary.
Just show’s you how daft his arguments are!
-
Disgraceful.
I used to have a great deal of respect for the guys started/maintain/edit this site but now i wonder what's gone wrong.
is steve away on vaction or something?
I'm just worried that someone with little experience might think the ideas put forward in this article are great and apply it in all subsequent projects...
s
-
I'm not sure if you are referring to my comments as disgraceful, or the original article? but I just wanted to make it clear that my comments were simply to demonstrate the Generic solution is lazy, and severly compromised.
The only solution is to use proper lookup tables, with foreign key references, that enable data integrity to be maintained!
Sorry Guys, I'll be less flippant in future!
-
Pretty sure he's talking about the article making its way onto such a well run, professional site.
Forgive me if im mistaken...
-
nonononono!
the original article is a disgrace.
it is my opinion that your approach is, perhaps, the only valid way to respond to such a bad design suggestion. your "one table design" will hopefully highlight the terrible nature of Ranga's article.
i totally agree that lookup tables (and tables in general) should have One Type Of Thing in them.
s
-
If anyone were to come into my company and see how these generic lookup tables have completely eradicated referential integrity and made data warehousing a total nightmare, they would stay well clear.
From my experience, generic lookup tables are produced by pressure from lazy developers who bully the dba guys into creating them.
Remeber, it's not always about data going in, you have to think how it's going to come out the otehr end.
------------------------------
The Users are always right - when I'm not wrong! -
*phew*
Glad to see I'm not the only one who reacted in horror to this article! Sadly, it seems that this has been seen by the editors - I just got this link as a FEATURED ARTICLE in my SQLServerCentral.com Daily Update newsletter.
Seriously though - this has been on the site for more than a month - someone needs to take this article down, before less savvy developers/users read it and follow it as gospel!
-
Guys,
I think this Might work for a very small application with a small number of lookup tables but any more than that it's just a bad idea... Doing so would 'paint yourself into a corner'
I had to support an app that was designed like this and there were constant changes to the table structure, and performance was a nightmare. Eventually some of the more critical data was moved out into individual tables.
Just say no! to bad design...
Mark
Viewing 15 posts - 1 through 15 (of 101 total)
You must be logged in to reply to this topic. Login to reply