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


A Lookup Strategy Defined


A Lookup Strategy Defined

Author
Message
Ivan Weaver
Ivan Weaver
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

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


bill_twomey
bill_twomey
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1916 Visits: 599

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





V1a
V1a
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 1
Thought of approaches like this many times and never used. Not really normalized and very confusing for
developers and others.



Jake Pretot
Jake Pretot
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

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


Paul McMahon
Paul McMahon
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 Visits: 19

Hilarious...

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

Says it all really...


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