Stairway to Database Design STEP 2: Domains, Constraints and Defaults

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Joe Celko » Stairway to Database Design STEP 2: Domains,...

Stairway to Database Design STEP 2: Domains, Constraints and Defaults

Rate Topic

Display Mode

Topic Options

Author

Message

CELKO

Posted Tuesday, May 04, 2010 12:04 AM

SSCommitted

Group: General Forum Members
Last Login: Tuesday, January 15, 2013 11:11 AM
Points: 1,945, Visits: 2,782

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

Tom Garth

Posted Tuesday, May 04, 2010 8:08 AM

SSC Eights!

Group: General Forum Members
Last Login: Friday, February 04, 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

Tom Wilson

Posted Tuesday, May 04, 2010 9:29 AM

SSC Journeyman

Group: General Forum Members
Last Login: Thursday, April 18, 2013 2:47 PM
Points: 85, Visits: 131

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

CELKO

Posted Tuesday, May 04, 2010 10:22 AM

SSCommitted

Group: General Forum Members
Last Login: Tuesday, January 15, 2013 11:11 AM
Points: 1,945, Visits: 2,782

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

Stefan Krzywicki

Posted Tuesday, May 04, 2010 1:30 PM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 4:47 PM
Points: 2,422, Visits: 6,025

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

Tom Bakerman

Posted Tuesday, May 04, 2010 3:05 PM

SSC Journeyman

Group: General Forum Members
Last Login: Friday, May 10, 2013 6:33 AM
Points: 76, Visits: 568

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

CELKO

Posted Wednesday, May 05, 2010 11:28 AM

SSCommitted

Group: General Forum Members
Last Login: Tuesday, January 15, 2013 11:11 AM
Points: 1,945, Visits: 2,782

-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

Tom Wilson

Posted Wednesday, May 05, 2010 12:43 PM

SSC Journeyman

Group: General Forum Members
Last Login: Thursday, April 18, 2013 2:47 PM
Points: 85, Visits: 131

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

Tom Bakerman

Posted Wednesday, May 05, 2010 2:25 PM

SSC Journeyman

Group: General Forum Members
Last Login: Friday, May 10, 2013 6:33 AM
Points: 76, Visits: 568

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

Carto Query

Posted Tuesday, August 24, 2010 9:36 AM

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

Permissions