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 Friday, February 20, 2004 12:26 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, December 6, 2005 11:59 PM
Points: 22, 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 .




Post #101387
Posted Friday, February 20, 2004 10:36 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, December 17, 2010 2:29 PM
Points: 1,016, Visits: 166

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.




Post #101515
Posted Friday, February 20, 2004 10:54 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, September 7, 2011 8:38 AM
Points: 28, 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


Post #101591
Posted Sunday, February 22, 2004 10:01 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, September 17, 2012 7:53 PM
Points: 43, 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.


Post #101666
Posted Sunday, February 22, 2004 10:27 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, September 17, 2012 7:53 PM
Points: 43, 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.


Post #101670
Posted Tuesday, February 24, 2004 1:27 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, December 9, 2014 5:36 AM
Points: 5,956, Visits: 286

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/
Post #101881
Posted Tuesday, February 24, 2004 6:39 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, September 7, 2011 8:38 AM
Points: 28, 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).


Post #101928
Posted Tuesday, February 24, 2004 7:32 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, December 9, 2014 5:36 AM
Points: 5,956, Visits: 286

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/
Post #101953
Posted Tuesday, February 24, 2004 4:23 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, September 17, 2012 7:53 PM
Points: 43, 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.


Post #102112
Posted Tuesday, February 24, 2004 4:32 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, September 7, 2011 8:38 AM
Points: 28, 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!


Post #102113
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse