February 18, 2003 at 12:00 am
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/dsumlin/lookupstrategy.asp
February 20, 2003 at 12:25 am
I must say. Simple idea for effective developing.
February 20, 2003 at 1:51 am
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
February 20, 2003 at 2:20 am
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.
February 20, 2003 at 2:24 am
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.
February 20, 2003 at 4:29 am
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
February 20, 2003 at 5:05 am
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
February 20, 2003 at 8:18 am
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 !!!!
February 20, 2003 at 10:03 am
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!
April 26, 2003 at 1:59 pm
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".
February 20, 2004 at 12:26 am
This approach becomes difficult if you need to enforce domain constraints within the database through the use of referential integrity. Such a solution forces then constraints into the program code rather than in the database. In a complex enterprise application this leads to poor data quality.
It also adds complexity if you have very complex reporting requirements .
February 20, 2004 at 10:36 am
You can also support multiple languages with this scheme by adding a LanguageID column and filtering on it (each distinct lookup item having the same LookGID but appearing with different LanguageID values). Of course, if you are not planning to translate everything, you then need a mechanism to pull out some default value (say English) when items are not found.
February 20, 2004 at 10:54 pm
I too use this concept very successfully. In fact I have 2 such tables, one for lookup values whose natural key is numeric, and one for values whose natural key is alphanumeric. From this you can correctly infer I don't use the LookGID column as I prefer to use the natural key instead. For example, I have school types of GVNS, GVSL, NGOT, etc - these values are meaningful to the user, so this is the natural key for SchoolType, and I therefore don't have to insert artificial values into the data in order to relate the value to its lookup descriptor. I have extended this concept into a data dictionary, where the lookup table and type are stored in a data dictionary - this is then used by my application to generate the necessary lookups to expand the values retrieved from the database into their corresponding descriptors. I
February 22, 2004 at 10:01 pm
I have been using a similar concept (since the days of COBOL 🙂 i.e. 1982 ), having a table ot CODE_TYPES and another one of CODES. I don't use identity columns if I can help it, using 'natural' keys wherever possible.
February 22, 2004 at 10:27 pm
Not if the values are few discrete values, such as state, which can be defined into the table definitions as a CHECK constraint. The CODE_TYPES and CODES tables I use are meant for front-end apps and reports wherever selection via drop-down list-boxes and the like is required.
Viewing 15 posts - 1 through 15 (of 34 total)
You must be logged in to reply to this topic. Login to reply