SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


A Lookup Strategy Defined


A Lookup Strategy Defined

Author
Message
sumlin
sumlin
SSC Journeyman
SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)

Group: General Forum Members
Points: 87 Visits: 1
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/dsumlin/lookupstrategy.asp



biteside
biteside
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 1
I must say. Simple idea for effective developing.



Jeremy Kemp
Jeremy Kemp
UDP Broadcaster
UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)

Group: General Forum Members
Points: 1448 Visits: 1
I totally agree. I developed something very similar 4 or 5 years ago and it saves so much development time.

We now have over 60 different types of codes and descriptions in this one table and only a few associated stored procedures (inserts, updates, deletes, returning individual values, lists of values etc.).

It only takes a couple of minutes to add a new set of codes and descriptions rather than develop the database table, the screens for maintaining entries and the stored procedures. I must have saved months in development time by using this approach.




Jeremy Kemp



well0549
well0549
Say Hey Kid
Say Hey Kid (681 reputation)Say Hey Kid (681 reputation)Say Hey Kid (681 reputation)Say Hey Kid (681 reputation)Say Hey Kid (681 reputation)Say Hey Kid (681 reputation)Say Hey Kid (681 reputation)Say Hey Kid (681 reputation)

Group: General Forum Members
Points: 681 Visits: 2
Just a few remarks because I recognize the problem but see a few problems with the solution..

1. Is the database diagram still readable ?
2. Are the queries where a couple of codes have to be translated still readable ?
aren't the joins confusing.



Keith Talbot
Keith Talbot
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 10
I too use a spookily similar table and have found it very effective. In particular moving new LookUps from dev to live is now very simple.

Small differences are that I have both a varchar and int field for the data part of the lookup and then use either or both of them.

I don't currently use the Const field which I shall now try, to aid readability.



arichard
arichard
Valued Member
Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)

Group: General Forum Members
Points: 57 Visits: 1

Simple design make great application!
I've got a similar design within the app I'm working on, with > 260 lookups for > 4000 values.

In addition to the fields you've mentionned in your design, I've got as well the following fields:
- an internal description, something which is never displayed to the end user but provides insider information for developers to what really is the value about, some info you may not want your end-user to see
- who has created and updated each record, as well the associated timestamp, which helps tracking changes in the application
- for lookup values, there's both a string value and an int value, where it's up to the code to decide to use one or both values

Even if this makes a much slicker DB design, there are 2 problems with this that I've accepted to live with:
[1] when you read a query, joins are not really explicit, if you're not properly specifying your aliases... to cope with that, I've got a script that automatically creates a view for each lookup type, with a meaningful name and restricts th e values to the lookup type you want; if these views are used in the queries, it makes them much easier to read, but there's some maintenance and the view overhead
[2] referential integrity... ok you can have a FK which will ensure that your main tables are linking to a valid value in your lookup table, but there's nothing that prevents assigning a value to a field outside of its type - say, if you've got a 'countries' and 'job types' lookups, you've got to ensure programmatically that you're not assigning a job type to a country field and vice-versa.

Arnaud Richard




Andy Warren
Andy Warren
SSC-Dedicated
SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)

Group: Moderators
Points: 30851 Visits: 2759
Nice article. I agree that it makes sense to put as much of it into one table as you can. We use a similar system, but we added a couple variations. One was that we wanted to support the idea of scope - some lists like state and country are global (sorry, to make this make sense, each of our customers has its own db) so we don't want to have to add a row to every db if a new one comes along. At the same time, a particular customer may well say that they only do business in North America, so we create a db scoped list just containing the values they use. Then there are the exceptions - so we can put values into a "campaign" scoped list. We control this through another lookup table that tells us what proc to execute and what params to pass to it - our app config program sets all that up. It's worked out fairly well. Our other goal in building it was emphasize our ability to cache the lists client side and only update if changed.

Saw a couple notes about using strings and numbers - anyone looked at using sql_variant instead?



Andy
http://www.sqlservercentral.com/columnists/awarren/

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Eric Buckley
Eric Buckley
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 Visits: 7
I also agree. Several years ago, I got very frustrated that I had a dozen lookup tables w/ no more that 20 rows. I felt like I was breaking some sacred tenet when I rolled them all into one table and threw a TYPE column in to categorize them.
I really like how you've taken the idea further !!!!



Miles Neale
Miles Neale
SSCarpal Tunnel
SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)

Group: General Forum Members
Points: 4712 Visits: 1695
Have been using this concept for almost 10 years. There is an added benefit we have noted in that when we allow a view of the combined code table, we have in play, that we can attempt standardization of codes across systems.

We are about to scope a step where we lift the combined code table out of the parent system and place it in a database by itself, with an associated simple input/update utility. There are issues we face such as the cascade of modifications but those can be overcome with a little patience and work.

Also we share a certain amount of data between various governmental groups. We are looking into an export device that will create an XML version of this data and make it available to those that are interested. With this in place we will be able to refer those who use our data to this one structure and one standard instead of multiples of each. So we have determined that not only is the concept a compact model for the use of data in a system but also a very tight model for deployment and distribution.

The article was great.

Not all gray hairs are Dinosaurs!
JohnBailey
JohnBailey
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 1
Saving time does not mean good design. Just the fact of forcing to have the same datatype for all lookup values is sufficient reason for no using it. But,... it will help people who "overnormalize".



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