Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Database Design and Reference Tables


Database Design and Reference Tables

Author
Message
Ranga N
Ranga N
SSC Journeyman
SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)

Group: General Forum Members
Points: 77 Visits: 146
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/rnarasimhan/databasedesignandreferencetables.asp



David le Quesne
David le Quesne
SSC Eights!
SSC Eights! (845 reputation)SSC Eights! (845 reputation)SSC Eights! (845 reputation)SSC Eights! (845 reputation)SSC Eights! (845 reputation)SSC Eights! (845 reputation)SSC Eights! (845 reputation)SSC Eights! (845 reputation)

Group: General Forum Members
Points: 845 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...
~nano
~nano
Old Hand
Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)

Group: General Forum Members
Points: 362 Visits: 113

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


Mark Firth-200666
Mark Firth-200666
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

Group: General Forum Members
Points: 21 Visits: 31

~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


Jason Steele
Jason Steele
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
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.


David Sette
David Sette
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

Group: General Forum Members
Points: 21 Visits: 118

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)


Boreades
Boreades
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 61

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.





Darren Beale
Darren Beale
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 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!
steven powell
steven powell
SSC-Enthusiastic
SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)

Group: General Forum Members
Points: 161 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





Darren Beale
Darren Beale
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 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!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search