SQLServerCentral / Discuss Content Posted by David Sumlin / Article Discussions / Article Discussions by Author / A Lookup Strategy Defined / Latest Posts

RE: A Lookup Strategy Defined

Paul McMahon — Tue, 15 Feb 2005 06:35:00 GMT

Hilarious...

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

Says it all really...

RE: A Lookup Strategy Defined

Jake Pretot — Tue, 13 Jul 2004 12:51:00 GMT

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

RE: A Lookup Strategy Defined

V1a — Wed, 24 Mar 2004 12:02:00 GMT

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

RE: A Lookup Strategy Defined

bill_twomey — Thu, 18 Mar 2004 09:27:00 GMT

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

RE: A Lookup Strategy Defined

Ivan Weaver — Thu, 18 Mar 2004 07:34:00 GMT

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.

RE: A Lookup Strategy Defined

bill_twomey — Wed, 17 Mar 2004 15:27:00 GMT

What are you doing with these lookups? Are you modelling business processes and/or enforcing referential integrity? Or are you populating drop downs?

You simply cannot sensibly combine all objects (OO sense, but not necessarily strict) into one table. You could not combine int and char types. Would you mingle apples and cats?

Are you populating a series of text drop downs, but not validating real relationships? I suppose commingling would be OK. But as soons as you want to validate, you're going back to the object sense and they should be separate.

Drop downs and lookups are relationships-light. As soons as you want real relationships, combined lookup and code tables are a nightmare.

Even if the DRI is reasonable, what's worse a bunch of tables and relationshisp tieing to one table, or a bunch of tables tieing to their own tables? I think people make too much of additional tables. Additional, cross-the-diagram relationships are worse. A decent modeling tool will allow you to have diagrams omitting low importance tables, so you won't be bothered by too many tables and their lookup tables.

Bill

RE: A Lookup Strategy Defined

gdefi — Tue, 09 Mar 2004 16:11:00 GMT

I think some people have got a wrong idea of a lookup table. It is not an entity per se. It holds discrete values and their meanings that are generally used for populating screens and reports, i.e. expanding on codes used within the system, where the codes may be "meaningful" to a few people but mumbo-jumbo to the rest of the populace. e.g. CustomerType=1 may mean something to the cheque-clearance personnel at the bank back-office, but be utterly meaningless to the customer himself. Therefore, in line with Paul, all a lookup table really needs is varchar.

RE: A Lookup Strategy Defined

Paul Thornett — Sat, 06 Mar 2004 17:05:00 GMT

I'd have thought most money and datetime values are not relevant to lookup tables. All my lookup codes are int (or some variation thereof), char or varchar, or yes/no. Data types like money and datetime do not need to be looked up - the data value is what the user wants to see. Or am I missing something?

RE: A Lookup Strategy Defined

Gabor Nyul — Fri, 05 Mar 2004 10:02:00 GMT

Just a small question:

How are you handling the different datatypes (eg. int, datetime money etc..) what you application would require?

Either you will use several columns of each required datatype (it means a lot of NULL values within your table or you will have to cast or convert the string value to the required datatype.

RE: A Lookup Strategy Defined

Frank Kalis — Wed, 25 Feb 2004 05:59:00 GMT

We're drifting off-topic.

Last one from me. When we run projections on the whole portfolio of our insured persons we deal with tens of millions of records. Yes, records, not rows, because none of the systems we've looked at utilizes a database but rather small flat files on the filesystem. These are highly specialized systems for asset liability management. Nobody expects response time <1 second. Or even one hour. Usually we start the run before we leave and it finishes somewhere during the night, so the results are available the next morning. What I mean to say, sometimes one is better of thinking off-database and consider the use of a dedicated software package.

RE: A Lookup Strategy Defined

Paul Thornett — Wed, 25 Feb 2004 05:48:00 GMT

The education database contains around 65,000 rows for each of 14 years. The users need the ability to compare, say, quartiles for several years by school type, and/or electorate, etc.

RE: A Lookup Strategy Defined

Frank Kalis — Wed, 25 Feb 2004 05:42:00 GMT

Sorry, forgot to add that when I mean I use Excel I didn't mean that I also use Excel's built-in functions, which I agree are very limited. We use a third-party function library.

RE: A Lookup Strategy Defined

Frank Kalis — Wed, 25 Feb 2004 05:31:00 GMT

I know Excel's limitation pretty well

I didn't mean I download hundreds of thousands of rows. The degree of accuracy doesn't grow significantly when you look at the whole population or take a sample beyond of say 30+ observations. That's quite handable for Excel.

As for Excel's habit of giving wrong answers. I made the experience that nobody's really interested in precise numbers as long as the wanted trend is there

In fact, try to explain to a marketing oriented, and therefore visually thinking CEO concepts like confidence intervals, standard error of the sample mean or finite population correction factors. At a best he doesn't listen to you. I know because I've tried (once)

RE: A Lookup Strategy Defined

Paul Thornett — Wed, 25 Feb 2004 05:12:00 GMT

"or simply download the data to Excel for any statistical stuff I do". Be very careful. First off, Excel can't handle more than 64,000 rows, so in many cases is not at all useful (whereas my SQL procedure is not constrained by numbers of rows or numbers of columns, and performs very fast for anything I have been able to throw at it). Second, take a look at this URL for far more learned opinions than I am qualified to give on Excel's infamous habit of giving wrong answers in many situations. Read the article, and consult the references section at the end to see what I mean. http://www.practicalstats.com/Pages/excelstats.html

RE: A Lookup Strategy Defined

Frank Kalis — Wed, 25 Feb 2004 01:22:00 GMT

Medians, Quartiles, Quintiles, Deciles, Percentiles. Moreover these measures are reported grouped by any number of columns (and I have never seen this capability anywhere else). In this context, the use of a cursor is mandatory!

Median as a measure of central tendency is okay when calculated in SQL Server. But as the median alone is not terribly useful, I generally prefer the use of a specialized software called Prophet or simply download the data to Excel for any statistical stuff I do. Because, as you have implied the code will become messy and might require a cursor (actually I never have thought about calculating the things you've mentioned in SQL Server)

As for gdefi:

I agree, each one to his taste.

RE: A Lookup Strategy Defined

Paul Thornett — Tue, 24 Feb 2004 16:32:00 GMT

I was over-simplifying when I wrote "deriving median values" . I have written a stored procedure which does any of the following, depending on the parameters passed: Medians, Quartiles, Quintiles, Deciles, Percentiles. Moreover these measures are reported grouped by any number of columns (and I have never seen this capability anywhere else). In this context, the use of a cursor is mandatory!

RE: A Lookup Strategy Defined

gdefi — Tue, 24 Feb 2004 16:23:00 GMT

I have a lot of respect for Joe Celko. However, I bend the rules sometimes. And look-up tables are precisely where I bend the rules, and the resulting CODES table, being used for the past four years, has stayed at 105 rows, has 14 types of codes, and has rarely changed. Its holds things like hearing types, action types, court types, plea types, legislation types, jurisdiction types, and the like (you've guessed it - it is an application to take care of legal processes). Each one to his taste, I suppose.

RE: A Lookup Strategy Defined

Frank Kalis — Tue, 24 Feb 2004 07:32:00 GMT

Use of a cursor for calculation of the median (financial or statistical ?) ???

CREATE TABLE #dist (c1 int)INSERT INTO #dist VALUES (2)INSERT INTO #dist VALUES (3)INSERT INTO #dist VALUES (1)INSERT INTO #dist VALUES (4)INSERT INTO #dist VALUES (8)INSERT INTO #dist VALUES (9)

SELECT Median=CASE COUNT(*)%2 WHEN 0 THEN (d.c1+MIN(CASE WHEN i.c1>d.c1 THEN i.c1 ELSE NULL END))/2.0 ELSE d.c1 END -- Odd numberFROM #dist d CROSS JOIN #dist iGROUP BY d.c1HAVING COUNT(CASE WHEN i.c1 <= d.c1 THEN 1 ELSE NULL END)=(COUNT(*)+1)/2

DROP TABLE #dist

Median ------------------- 3.500000

or if there are duplicate values

set nocount onCREATE TABLE #dist (c1 int)INSERT #dist VALUES (2)INSERT #dist VALUES (2)INSERT #dist VALUES (1)INSERT #dist VALUES (5)INSERT #dist VALUES (5)INSERT #dist VALUES (9)

SELECT Median=ISNULL((CASE WHEN COUNT(CASE WHEN i.c1<=d.c1 THEN 1 ELSE NULL END) > (COUNT(*)+1)/2 THEN 1.0*d.c1 ELSE NULL END)+COUNT(*)%2, (d.c1+MIN((CASE WHEN i.c1>d.c1 THEN i.c1 ELSE NULL END)))/2.0)FROM #dist d CROSS JOIN #dist iGROUP BY d.c1HAVING (COUNT(CASE WHEN i.c1 <= d.c1 THEN 1 ELSE NULL END)>=(COUNT(*)+1)/2)AND (COUNT(CASE WHEN i.c1 >=d.c1 THEN 1 ELSE NULL END) >= COUNT(*)/2+1)GOdrop table #dist

Median ---------------- 3.5

There are many other ways possible. None of them utillizes a cursor.

RE: A Lookup Strategy Defined

Paul Thornett — Tue, 24 Feb 2004 06:39:00 GMT

Joe Celko is very much of a purist when it comes to relational databases. If you have ever tried to adopt the code examples he provides in his books, you'll soon realize that they tend to run very slowly if at all. In his "SQL for Smarties" book he provides several different ways of deriving median values. Some of this code doesn't work at all, while other code runs excruciating slowly (he doesn't approve of the use of cursors, which IMHO is the only practical way of addressing this particular problem).

RE: A Lookup Strategy Defined

Frank Kalis — Tue, 24 Feb 2004 01:27:00 GMT

I am not going with the author or the mainstream here.

As I'm too tired to write a pamphlet and in addition have only a very limited english vocabulary compared to the author of the reply in the link below, read this

http://groups.yahoo.com/group/sql_server7_forum/message/5731

It's a reply to this question

http://groups.yahoo.com/group/sql_server7_forum/message/5726

RE: A Lookup Strategy Defined

gdefi — Sun, 22 Feb 2004 22:27:00 GMT

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.

RE: A Lookup Strategy Defined

gdefi — Sun, 22 Feb 2004 22:01:00 GMT

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.

RE: A Lookup Strategy Defined

Paul Thornett — Fri, 20 Feb 2004 22:54:00 GMT

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

RE: A Lookup Strategy Defined

Kevin Hood — Fri, 20 Feb 2004 10:36:00 GMT

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.

RE: A Lookup Strategy Defined

Kerry Bennett-69470 — Fri, 20 Feb 2004 00:26:00 GMT

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 .

RE: A Lookup Strategy Defined

JohnBailey — Sat, 26 Apr 2003 13:59:00 GMT

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".

RE: A Lookup Strategy Defined

Miles Neale — Thu, 20 Feb 2003 10:03:00 GMT

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.

RE: A Lookup Strategy Defined

Eric Buckley — Thu, 20 Feb 2003 08:18:00 GMT

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 !!!!

RE: A Lookup Strategy Defined

Andy Warren — Thu, 20 Feb 2003 05:05:00 GMT

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?Andyhttp://www.sqlservercentral.com/columnists/awarren/

RE: A Lookup Strategy Defined

arichard — Thu, 20 Feb 2003 04:29:00 GMT

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 valuesEven 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

RE: A Lookup Strategy Defined

Keith Talbot — Thu, 20 Feb 2003 02:24:00 GMT

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.

RE: A Lookup Strategy Defined

well0549 — Thu, 20 Feb 2003 02:20:00 GMT

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.

RE: A Lookup Strategy Defined

Jeremy Kemp — Thu, 20 Feb 2003 01:51:00 GMT

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

RE: A Lookup Strategy Defined

biteside — Thu, 20 Feb 2003 00:25:00 GMT

I must say. Simple idea for effective developing.

A Lookup Strategy Defined

sumlin — Tue, 18 Feb 2003 00:00:00 GMT

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/dsumlin/lookupstrategy.asp