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 «««7891011»»

Database Design and Reference Tables Expand / Collapse
Author
Message
Posted Wednesday, September 27, 2006 9:07 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, January 7, 2010 6:52 PM
Points: 516, Visits: 46
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.
Post #311567
Posted Wednesday, September 27, 2006 9:56 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Monday, February 9, 2015 7:07 AM
Points: 199, Visits: 60
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!!!


Post #311592
Posted Wednesday, September 27, 2006 12:02 PM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Thursday, April 7, 2011 10:03 AM
Points: 149, Visits: 76

My question to Steve is Why did he repost a bad article with clearly misleading information.  I would take it off the server.

 

Post #311639
Posted Wednesday, September 27, 2006 12:09 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Wednesday, August 26, 2015 12:33 PM
Points: 20,627, Visits: 9,655

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

Post #311641
Posted Wednesday, September 27, 2006 1:41 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 12:34 PM
Points: 1,624, Visits: 1,578

Rarely have I disagreed more with an article I've read on here.

Advantages of "GenericLookUp" Table

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.

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
StateIDStateAbbreviationStateName
1ALAlabama
2CACalifornia
3TXTexas
4FLFlorida
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
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring
Microsoft Certified Master, SQL Server MVP
Principal Database Architect (consultant) at DB Best Technologies
Post #311681
Posted Wednesday, September 27, 2006 1:47 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Wednesday, August 26, 2015 12:33 PM
Points: 20,627, Visits: 9,655

Amen.

 

Hey this is the first time I post the 100th message on a thread .

Post #311684
Posted Wednesday, September 27, 2006 3:11 PM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Monday, February 9, 2015 7:07 AM
Points: 199, Visits: 60

Sounds like the new Access engine will help the guys out that want a poor design...

http://www.pcw.co.uk/articles/print/2163161




Post #311709
Posted Thursday, September 28, 2006 8:33 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Saturday, August 8, 2015 9:29 AM
Points: 50, Visits: 297
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!
Post #311901
Posted Thursday, September 28, 2006 8:49 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, August 24, 2015 4:00 PM
Points: 2,555, Visits: 622
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 !!!**
Post #311913
Posted Wednesday, October 11, 2006 8:08 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, January 27, 2014 8:18 PM
Points: 1, Visits: 17
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
Post #314803
« Prev Topic | Next Topic »

Add to briefcase «««7891011»»

Permissions Expand / Collapse