SQL relations considered harmful? - ISerializable - Roy Osherove's Blog

SQL relations considered harmful?

OK. So I'm no SQL server guru but I do consider myself to know *something* about it. Which is why it was really weird to read this post and realize that I have no idea what that guy was talking about. Domains? Not using a relation in favor of exporting domain views? using CHECK constraints instead of relations? I know CHECK constraints but funnily enough I was never in a position to actually use them for these kinds of things. Guess that comes with not knowing the territory..
 
How widespread are these methods? when should I use them? are there any specific guidelines that talk about this issue in particular somewhere where I can get more enlightenment?
Published Wednesday, February 18, 2004 12:31 AM by RoyOsherove
Filed under:

Comments

Tuesday, February 17, 2004 5:54 PM by Darrell

# re: SQL relations considered harmful?

Domains
Each column in a table holds data of a single data type and length, specified when the column is created or altered. The data type and length may be specified explicitly, e.g. CHARACTER(20) or INTEGER(5), or through the use of domains, which can give more precise control over the data that will be accepted in the column.

A domain definition consists of a data type and length specification with optional check conditions and a default value. Data which falls outside the constraints defined by the check conditions is not accepted in a column which is defined using the domain.

A column defined using a domain for which a default value is defined will automatically receive that value if row data is entered without a value being explicitly specified for the column.

I got this from here (http://developer.mimer.com/documentation/latest_html/Mimer_SQL_Engine_DocSet/Mimer_Concepts15.html).
Tuesday, February 17, 2004 5:59 PM by Damian

# re: SQL relations considered harmful?

What David was talking about is not that relations are bad (far, far, far from it if you know Dave) but creating a lookup table that has an identity column to enforce a couple of seldom changing values isn't a relational best practice.

This of course, is the subject of years of arguments, and will continue to be :)

Read this http://www.sqlteam.com/item.asp?ItemID=2599 and the attached comments for a flame fest ^H^H^H^H^H^H^H^H^H^H^H^H^H^H err, thorough treatment of each side.


Tuesday, February 17, 2004 6:01 PM by Darrell

# re: SQL relations considered harmful?

Oh, and he wasn't trading a check constraint for a relation, he removed the lookup table and enforced the permissible column values with a check constraint. That's ok if you only have a few values (as in his example, M/F/U). Lookup tables are good for more values and when the user wants to change them all the time.

He does push a composite primary key, which has its pros and cons. I hate them since they can usually be updated, which can cascade changes throughout your database if things go wrong. After I killed a database that way, I don't do it anymore. A database also takes much more of a performance hit if joining on tables with composite keys (depending on size).

An alternative I prefer is to have an identity primary key with a unique index on the four columns in his example. This gives me the performance I need, the ease of use I want, and the business rule enforcement necessary. A little more work, but not much.
Tuesday, February 17, 2004 7:06 PM by Udi Dahan - The Software Simplist

# re: SQL relations considered harmful?

Darell,

I too have gone the identity + unique index route, and haven't looked back. I guess that when it comes to lookup tables, my first choice IS to have one, seeing as my users over time ( rather short periods of time actually ) add more and more values. In cases where I have M/F type things that really aren't gonna change much, then I use check constrainsts there.
Tuesday, February 17, 2004 7:25 PM by Paul Wilson

# re: SQL relations considered harmful?

Anyone working in the medical field can attest that even simple things like gender are not so obvious. I've seen standards that define 5 or 6 gender codes, and there's nothing like different medical facilities insisting on their own set of gender codes before they will buy your software. I'm just using this as a silly example of how even the simplest of cases is not so simple afterall, so I very much appreciate lookup tables based on experience in the real world over the technical wisdom of the academic.
Tuesday, February 17, 2004 8:44 PM by DavidM

# re: SQL relations considered harmful?

Darell,

Yourself and Udi are the exception and not the rule. At least you have identified the real key and enforced it. The table in question did not have any RI against it so adding a Identity as a surrogate key was pointless. Actually, the whole point was the fallacy of an Identity columns as a "real" key as well as the total confusion most developers make between types and relations.

I just wrote an exntension to that post where I address the whole lookup/domain issue. http://weblogs.sqlteam.com/davidm/archive/2004/02/18/907.aspx

And Paul, the medical systems I have been involved with had every possible chromosomal difference in them for gender.. I took that up with the medical board at the time and the values where nicely debated and laid out. A bit of leg work goes a long way to making a good database....
Thursday, February 19, 2004 6:12 PM by TrackBack

# Take Outs: The Digital Doggy Bag of Blog Bits for 18th and 19th February 2004

Take Outs: The Digital Doggy Bag of Blog Bits for 18th and 19th February 2004