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 12»»

Stairway to Database Design STEP 2: Domains, Constraints and Defaults Expand / Collapse
Author
Message
Posted Tuesday, May 4, 2010 12:04 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 7:59 PM
Points: 1,945, Visits: 3,080
Comments posted to this topic are about the item Stairway to Database Design STEP 2: Domains, Constraints and Defaults

Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #915087
Posted Tuesday, May 4, 2010 8:08 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, February 4, 2011 7:20 AM
Points: 977, Visits: 1,499
Good article Joe. I think you might want to add a link to your Step 1 article.

This is a note and not a criticism: Many people do math with temperatures. Add 1 BTU to 1 pound of water at 32 degrees and the new temperature will be 32 degrees + 1 degree. However math isn't done on "Customer Numbers" or "Item Numbers".

Thanks.


Tom Garth
Vertical Solutions

"There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers
Post #915334
Posted Tuesday, May 4, 2010 9:29 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, September 22, 2014 12:12 PM
Points: 86, Visits: 142
Two comments on the comment
1. There are easier examples of how much legitimately can be done with temperatures. First, in some ways temperature is like the date domain - it makes perfect sense to subtract two temperatures (an interval result), while other arithmetic operations make less sense. Second, there are plenty of places in thermodynamics where arithmetic is done using temperatures (in degrees Kelvin). The maximum efficiency of a heat engine operating between two temperatures is (T2 - T1)/T2. The ideal gas law is PV=nRT; here temperature takes part in multiplication (although not by other temperatures.)

Thinking about it, I have a hard time coming up with a legitimate numeric domain that does not allow at least something lilke interval calculations. Something less amenable to arithmetic would be 1-5 ranking for like/dislike. Though we often compute an average, that is probably not legitimate unless one assumes that like/dislike is a linear scale.

2. In my opinion, Customer Numbers and Items Numbers don't belong to the numeric domain. I usually argue that these are string values which are restricted to digit characters. As just one argument, there are generally minimum and maximum length restrictions on such values which are hard to specify for integers, but are obvious for strings.
Post #915409
Posted Tuesday, May 4, 2010 10:22 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 7:59 PM
Points: 1,945, Visits: 3,080
Many people do math with temperatures. Add 1 BTU to 1 pound of water at 32 degrees and the new temperature will be 32 degrees + 1 degree. However math isn't done on "Customer Numbers" or "Item Numbers".


Temperature always needs mass or volume, pressure, time and maybe something else for math to make sense. The five-point preference scale (aka Likert scale) has another problem; people can have non-transitivity when presented in pairs (ever play "scissor, paper, stone" ?).


Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #915457
Posted Tuesday, May 4, 2010 1:30 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 1:48 PM
Points: 844, Visits: 7,393
Temperature always needs mass or volume, pressure, time and maybe something else for math to make sense.


This seems to be a non-sequiter. You said in the article
While I can add, subtract, multiply and divide integers, it makes no sense to do math like that on temperatures.

Adding in criteria like that after the fact indicates the original statement was lacking. And needing other criteria doesn't mean "it makes no sense to do math like that on temperatures", it means you need other criteria and then you DO do math like that on temperatures.
Average high temp for a given day, average body temp for patients presenting specific conditions, average boiling temp for a substance given a constant starting mass, volume, etc... all of these are simply averages of temperatures, the other variables are there, but don't actually affect the math done to average the temperatures.


--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
Post #915610
Posted Tuesday, May 4, 2010 3:05 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 8:15 AM
Points: 115, Visits: 813
Nice article.

Can you explain what this means? Is there a typo somewhere?

floob_score INTEGER CHAR(5) NOT NULL

CHECK (CASE WHEN floob_score NOT BETWEEN 1 AND 99 THEN 'F'

WHEN floob_score = 42

AND fuzz_nbr = 17

THEN 'T' ELSE 'F' END = T'),


Is it supposed to be:
floob_score INTEGER,
fuzz_nbr INTEGER,
valid_floob CHAR(5) NOT NULL

CHECK (CASE WHEN floob_score NOT BETWEEN 1 AND 99 THEN 'F'

WHEN floob_score = 42

AND fuzz_nbr = 17

THEN 'T' ELSE 'F' END)


Post #915682
Posted Wednesday, May 5, 2010 11:28 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 7:59 PM
Points: 1,945, Visits: 3,080
-No, CASE is an expression and returns a scalar value. That is they we need a theta operator to get a truth value in the CHECK() constraint:

CHECK ( CASE WHEN .. THEN 'T' ELSE 'F' END = 'T')


Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #916309
Posted Wednesday, May 5, 2010 12:43 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, September 22, 2014 12:12 PM
Points: 86, Visits: 142
I think there are points made by each poster that are missed by the other in the last two posts. Here's the code from the article:

floob_score INTEGER CHAR(5) NOT NULL
CHECK (CASE WHEN floob_score NOT BETWEEN 1 AND 99 THEN 'F'
WHEN floob_score = 42
AND fuzz_nbr = 17
THEN 'T' ELSE 'F' END = T'),

1. Joe - The first line looks like something is extra. It is a declaration for floob_score, but has both integer and char(5) for the data type. It would be OK if the char(5) was not there. This is part of the confusion with the example.

2. Tom - The CHECK() constraint is the intended expression (except for the missing single-quote in front of the final T). Joe is correct in that you can't drop the final ='T'. But Joe - the CASE expression involves two variables - floob_score and fuzz_nbr - making it a table constraint, not a single-column constraint. At the very least, this requires a comma after the NOT NULL on the first line, making the check constraint be an un-named table constraint, and fuzz_nbr just not appearing in the example.

My suggestion for what it is meant to look like is the following.

fuzz_nbr INTEGER NOT NULL,
floob_score INTEGER NOT NULL,
CHECK (CASE WHEN floob_score NOT BETWEEN 1 AND 99 THEN 'F'
WHEN floob_score = 42
AND fuzz_nbr = 17
THEN 'T' ELSE 'F' END = 'T'),

Post #916359
Posted Wednesday, May 5, 2010 2:25 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 8:15 AM
Points: 115, Visits: 813
Thanks. It was that extra CHAR(5) that was throwing me off.

And for readability, would it help to put some extra () like this:

CHECK ((CASE WHEN floob_score NOT BETWEEN 1 AND 99 THEN 'F'
WHEN floob_score = 42
AND fuzz_nbr = 17
THEN 'T' ELSE 'F' END) = 'T')

Post #916473
Posted Tuesday, August 24, 2010 9:36 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, October 22, 2012 6:30 AM
Points: 7, Visits: 33
Excellent series! A good look at many basic principles. Excuse the late post, but I'm reading all four articles together.

In Step 2, you mention the three major SQL data types: numeric, string & temporal. You mention that there are other data types in SQL, but refer to these as the "Big Three".

As a GIS (Geographic Information Systems) professional who is only just migrating our database environment from MS Access (gasp!) to SQL Server 2008, I was a little sorry not to see any reference to the newly implemented Spatial data type. This is something that our office is excited about taking advantage of in order to make our data truly non-proprietary. Can you offer any explanation?

Thanks,
Tammy
Post #974232
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse