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 «««1234

A Lookup Strategy Defined Expand / Collapse
Author
Message
Posted Thursday, March 18, 2004 7:34 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, February 21, 2013 1:07 PM
Points: 32, Visits: 50

All I can say is 'COOOL', its a great way to simplify cross-referencing.  You only have one place to look.  With a couple ASP pages or VB forms the management becomes almost routine.

I haven't read all the posts in the thread yet, but wanted to add my 2c to start.

I have figured out how/where I can implement the concept in a couple of my applications currently in design/development without affecting my timeline - especially if we find out we need more lookup tables..err..references.

Post #106843
Posted Thursday, March 18, 2004 9:27 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, April 07, 2014 7:46 AM
Points: 1,895, Visits: 394

You got any space saving or performance improvement with this scheme?  Do you have less code to maintain?

Any chance you'll be locking everyone out of all lookup values while you're maintaining it? 

Is your maintenance code better?  You definitely waste space, you mishandle numeric lookup values, and you can't handle referential integrity well. 

You may also create security issues, maintenance issues and disregard normalization. 

Suppose certain users can have access to a set set of lookup values - you need views.  Suppose some users need access to a subset of lookup values, now you have to carry an unused subtype around for all the other lookup values. 

How are you filtering lookup values for retrieval?  You are using complicated views or stored procs instead of a number of simple ones. 

So you have a possibly handy, but definitely poor design.  You're also putting all your  eggs in one basket - what happens when some accidently clobbers the combined lookup table?  Everything is down.

see http://www.tdan.com/i016ht03.htm  and http://www.dbmsmag.com/9802d06.html




Post #106879
Posted Wednesday, March 24, 2004 12:02 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, April 04, 2007 7:17 PM
Points: 1, Visits: 1
Thought of approaches like this many times and never used. Not really normalized and very confusing for
developers and others.



Post #108033
Posted Tuesday, July 13, 2004 12:51 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 21, 2007 12:47 PM
Points: 1, Visits: 1

While this design is appealing because of reducing the number of tables, it creates additional work to handle lookups. The creation of multiple views is an example of this. Also, I would think performace would be reduced because of using indecies on varchar fields you call constants vs. using an integer key. I did however, very much enjoy your article.

-Jake

Post #126136
Posted Tuesday, February 15, 2005 6:35 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, September 17, 2012 6:27 AM
Points: 27, Visits: 16

Hilarious...

http://www.sqlservercentral.com/columnists/dpeterson/lookuptablemadness.asp

Says it all really...

Post #161696
« Prev Topic | Next Topic »

Add to briefcase «««1234

Permissions Expand / Collapse