Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

Database Design and Reference Tables Expand / Collapse
Author
Message
Posted Thursday, August 25, 2005 2:02 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, October 18, 2013 11:20 AM
Points: 67, Visits: 135
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/rnarasimhan/databasedesignandreferencetables.asp


Post #214096
Posted Tuesday, September 27, 2005 1:06 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, August 10, 2010 4:54 AM
Points: 815, Visits: 32

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...
Post #223797
Posted Tuesday, September 27, 2005 1:13 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, November 12, 2013 3:55 AM
Points: 329, Visits: 102

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

Post #223798
Posted Tuesday, September 27, 2005 2:34 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, July 15, 2013 3:22 AM
Points: 19, Visits: 30

~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

 

 

Post #223807
Posted Tuesday, September 27, 2005 2:39 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 16, 2009 11:16 AM
Points: 4, Visits: 11

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.

 

Post #223809
Posted Tuesday, September 27, 2005 2:45 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: 2 days ago @ 8:26 AM
Points: 19, Visits: 78

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)

Post #223810
Posted Tuesday, September 27, 2005 3:19 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 06, 2013 5:04 AM
Points: 9, Visits: 45

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.




Post #223815
Posted Tuesday, September 27, 2005 3:44 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, January 23, 2006 8:40 AM
Points: 11, Visits: 1
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!
Post #223824
Posted Tuesday, September 27, 2005 4:37 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, May 12, 2009 9:33 AM
Points: 157, Visits: 2

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




Post #223833
Posted Tuesday, September 27, 2005 4:44 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, January 23, 2006 8:40 AM
Points: 11, Visits: 1
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!
Post #223834
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse