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 Tuesday, February 18, 2003 12:00 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, December 9, 2003 12:00 AM
Points: 21, Visits: 1
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/dsumlin/lookupstrategy.asp


Post #10072
Posted Thursday, February 20, 2003 12:25 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, March 26, 2004 1:25 AM
Points: 1, Visits: 1
I must say. Simple idea for effective developing.


Post #54505
Posted Thursday, February 20, 2003 1:51 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, March 23, 2006 6:53 AM
Points: 348, 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




Post #54506
Posted Thursday, February 20, 2003 2:20 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, August 31, 2008 12:56 PM
Points: 281, 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.






Post #54507
Posted Thursday, February 20, 2003 2:24 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 5, 2008 12:24 PM
Points: 5, 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.




Post #54508
Posted Thursday, February 20, 2003 4:29 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 24, 2003 12:00 AM
Points: 3, 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





Post #54509
Posted Thursday, February 20, 2003 5:05 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Friday, August 29, 2014 8:46 AM
Points: 6,783, Visits: 1,881
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
Post #54510
Posted Thursday, February 20, 2003 8:18 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, April 1, 2010 12:43 PM
Points: 14, 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 !!!!




Post #54511
Posted Thursday, February 20, 2003 10:03 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, August 29, 2014 10:04 AM
Points: 2,299, Visits: 1,356
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!
Post #54512
Posted Saturday, April 26, 2003 1:59 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, April 26, 2003 12:00 AM
Points: 1, 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".


Post #54513
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse