Post reply

A Lookup Strategy Defined

  • March 18, 2004 at 7:34 am

    #499325

    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.

  • March 18, 2004 at 9:27 am

    #499352

    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

  • March 24, 2004 at 12:02 pm

    #500285

    Thought of approaches like this many times and never used. Not really normalized and very confusing for

    developers and others.

  • July 13, 2004 at 12:51 pm

    #514449

    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

  • February 15, 2005 at 6:35 am

    #542257

    Hilarious...

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

    Says it all really...

Viewing 5 posts - 31 through 34 (of 34 total)

You must be logged in to reply to this topic. Login to reply