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 Thursday, February 23, 2006 12:41 PM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Monday, June 23, 2014 7:56 AM
Points: 199, Visits: 57

I think of this as an example of a FoxPro database programmer that thinks he has graduated to SQL Server.  While it will work for small implementations and in small minds, this is REALLY bad design. PERIOD.

Personally I vote for going to 5th normal. 

I do that and then back off, maybe to 3rd, but not less than that.  Unless of cource, I am working on a data warehouse/mart project.

 

Remember it is always easier to denormalize through views than it is to normalize a bad structure.




Post #261118
Posted Wednesday, September 27, 2006 7:40 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, October 15, 2010 2:34 PM
Points: 9, Visits: 5

My first issue:

I often use lookup tables for more than one other table . What would I put in the strCategory column? I would need a child table for multiple categories.

My second was covered in ...madness.asp (maybe my first was covered in other posts. I didn't read ALL of them.)

Post #311521
Posted Wednesday, September 27, 2006 8:04 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Monday, June 23, 2014 7:56 AM
Points: 199, Visits: 57

What kind of category?

Depending on how they are used, you can use a lookup across multiple tables.  Category for example could have 3 colums and be related to another table, I.E.

tlkpCategoryGroup           tlkpCategory

CategoryGroupID             CategoryID

CagetoryGroupDesc         CategoryGroupID

                                   CategoryDesc

Then you can limit what it lists for the multiple uses of it.




Post #311535
Posted Wednesday, September 27, 2006 8:19 AM


SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Monday, September 9, 2013 8:32 AM
Points: 108, Visits: 166

The "single lookup" may or may not make sense in anyone's particular situation.  What stuns me is that it has become a "hot issue" - yet 90%of the database programmers I meet haven't got even a passing familiarity with relational database theory and practice.  After 25 years of it, let me point out you can always choose to ignore theory once you know it.

There is no one solution to normalizing data, and this may be right for some places.  But it might be an interesting excercise to take it all the way to sixth normal form (which you'd never place in production) and then recombine to fifth - since there is probably more than one way to do it, and this will tend to reveal more possibilities as you take the totally decomposed objects and then make sensible objects from them.

Please, don't forget to read your Codd & Date, and Stonebreaker, etc, while we are reading all the books we need just to "get the work done".   You WILL make better decisions in design.

But why are we even argueing about consolidated lookup tables?  Doesn't make much sense to me, but I don't know your data.



Roger L Reid
Post #311546
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, June 23, 2014 7:56 AM
Points: 199, Visits: 57
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: Yesterday @ 11:08 PM
Points: 21,385, Visits: 9,602

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: Friday, July 25, 2014 10:16 AM
Points: 1,612, Visits: 1,537

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 2008
Principal DBA: Outerwall, Inc.
Also available for consulting: SQL DBA Master
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: Yesterday @ 11:08 PM
Points: 21,385, Visits: 9,602

Amen.

 

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

Post #311684
« Prev Topic | Next Topic »

Add to briefcase «««7891011»»

Permissions Expand / Collapse