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 Wednesday, February 25, 2004 1:22 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, August 13, 2013 1:18 AM
Points: 5,956, Visits: 285

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.

 



--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/
Post #102151
Posted Wednesday, February 25, 2004 5:12 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
"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


Post #102196
Posted Wednesday, February 25, 2004 5:31 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, August 13, 2013 1:18 AM
Points: 5,956, Visits: 285

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)



--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/
Post #102200
Posted Wednesday, February 25, 2004 5:42 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, August 13, 2013 1:18 AM
Points: 5,956, Visits: 285

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.

 



--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/
Post #102203
Posted Wednesday, February 25, 2004 5:48 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
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.


Post #102206
Posted Wednesday, February 25, 2004 5:59 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, August 13, 2013 1:18 AM
Points: 5,956, Visits: 285

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.

 



--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/
Post #102210
Posted Friday, March 5, 2004 10:02 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Wednesday, February 11, 2009 8:57 AM
Points: 619, Visits: 8

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.





Bye
Gabor
Post #104663
Posted Saturday, March 6, 2004 5:05 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'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?


Post #104816
Posted Tuesday, March 9, 2004 4:11 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 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.



Post #105260
Posted Wednesday, March 17, 2004 3:27 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, July 28, 2014 6:51 AM
Points: 1,895, Visits: 421

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




Post #106715
« Prev Topic | Next Topic »

Add to briefcase «««1234»»

Permissions Expand / Collapse