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


A Lookup Strategy Defined


A Lookup Strategy Defined

Author
Message
Kerry Bennett-69470
Kerry Bennett-69470
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 1

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 .





Kevin Hood
Kevin Hood
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1020 Visits: 167

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.





Paul Thornett
Paul Thornett
SSC Journeyman
SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)

Group: General Forum Members
Points: 92 Visits: 96
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



gdefi
gdefi
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 Visits: 38
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.



gdefi
gdefi
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 Visits: 38
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.



Frank Kalis
Frank Kalis
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11059 Visits: 289

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



--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/
Paul Thornett
Paul Thornett
SSC Journeyman
SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)

Group: General Forum Members
Points: 92 Visits: 96
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).



Frank Kalis
Frank Kalis
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11059 Visits: 289

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 number
FROM #dist d CROSS JOIN #dist i
GROUP BY d.c1
HAVING 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 on
CREATE 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 i
GROUP BY d.c1
HAVING (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)
GO
drop table #dist

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

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



--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/
gdefi
gdefi
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 Visits: 38
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.



Paul Thornett
Paul Thornett
SSC Journeyman
SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)

Group: General Forum Members
Points: 92 Visits: 96
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!



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