Relational Theory Question - Domains

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

Other Topics

Relational Theory Question - Domains

Author

Topic

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-08-04 : 17:42:56

I know SQL Server doesn't support true domains, but I want to make sure that I understand them correctly.

Let's say I have two attributes (columns), height and weight. I decide I want to create separate domains for them. They'd both be the same numeric type, same scale/precision, same defaults, same constraints on values. My question is whether these two domains can be compared to one another, say in a JOIN or WHERE condition? I remember reading somewhere that different domains would not allow this, or would at least not allow an implicit cast on a JOIN. Can't remember exactly but it was probably something Chris Date wrote.

I tested this in PostGRESQL and they did indeed join on value, so it appears not to be the case. Can anyone confirm?

And as a bonus question, can anyone figure a way to enforce this kind of domain separation in SQL Server? Especially without using CLR data types? I've been experimenting with sql_variant and have had some luck, but I still can't avoid implicit casts to base data types.

Thanks.

dmilam
Posting Yak Master

185 Posts

Posted - 2010-08-12 : 19:39:23

Not sure why you want to create separate domains, rather than just keep the two attributes as the same type. As far as I know, domain is just another word for type.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-08-12 : 23:21:52

quote:
As far as I know, domain is just another word for type.

That's the point, domains are not just types, and I specifically want them to be separate.

dmilam
Posting Yak Master

185 Posts

Posted - 2010-08-13 : 14:11:00

quote:
Originally posted by robvolk

quote:
As far as I know, domain is just another word for type.
That's the point, domains are not just types, and I specifically want them to be separate.

But in a non-vendor-specific sense, domains are types, albeit of arbitrary complexity. At least this is what I gather from Date. But how to deal with such complexity, I don't know.

Post the code? I'm curious how this could be done, setting aside why complexity is needed.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-08-13 : 15:48:40

Here ya go (PostGres syntax):

CREATE DOMAIN height_cm
  AS numeric(4,1)
  DEFAULT 0
  NOT NULL
   CONSTRAINT height_cm_check CHECK ((VALUE >= (0)::numeric));
ALTER DOMAIN height_cm OWNER TO postgres;

CREATE DOMAIN weight_kg
  AS numeric(4,1)
  DEFAULT 0
  NOT NULL
   CONSTRAINT weight_kg_check CHECK ((VALUE >= (0)::numeric));
ALTER DOMAIN weight_kg OWNER TO postgres;

Regarding types, it's probably a nomenclature error. I'm talking about types vs. domains in PostGres/SQL Server/ANSI SQL. I believe a SQL Domain = Relational Type (it's been a while since I read Date). The major difference is that a domain also describes valid and invalid values and optional default values, in addition to the physical data type.

As far as SQL Server goes, you can combine rules, bound defaults and possibly user-defined data types to get most of what a domain would provide, except for the domain isolation/separation I was asking about. I probably read the original definition of domain wrong.

As to "why complexity is needed", I'm not trying to make things more complex. There are many kinds of attributes that are numerical measures, e.g. height/length, weight, time intervals, age, monetary values, quantities; however none of these are comparable measures as they have different (or no) units of measure. In other words, they inhabit different domains. My goal isn't just to prevent joining incomparable domains, but to describe them clearly in a data model so that their differences are explicit, even if their underlying data types are equivalent.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-08-13 : 16:54:06

I get what you're saying about the isolation/seperation.

Still you can create a user defined type, assigned to a specific schema, with a check constraint to accomplish all of that. You can assign permissions to the schema as required. This I've done on a small handful of occassions.

Perhaps better for you is to create a CLR user defined type. I've never done that though, so I won't be much help there

edit -- after reading your original post, never mind the CLR part of my comment

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-08-13 : 17:32:23

I'm not against using CLR to accomplish it, I was just hoping it wasn't necessary.

In case anyone's interested, I found the book I was trying to remember:

http://www.amazon.com/SQL-Relational-Theory-Write-Accurate/dp/0596523068

Chapter 2 covers Types and Domains, and he specifically says they're identical, with Type as his preferred term. From the book:

quote:
From this point forward I'll favor the term type over the term domain. So what is a type, exactly? In essence, it's a named, finite set of values[17] —all possible values of some specific kind: for example, all possible integers, or all possible character strings, or all possible supplier numbers...

He has a very detailed example of what I originally described (he used weight and quantity though) to show that those 2 types are not the same even though they're both numbers. He also said Codd had relaxed or rewritten his rules about domains so that they could allow comparisons between them, and that he doesn't agree with it. I guess that's why PostGres allows that kind of join, because it's likely ANSI specifies it.

I like separating UDTs in schemas but it won't prevent the implicit conversion (Date calls this coercion) to the base types. I'm not entirely sure a CLR type would prevent it either, unless it's a composite type.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-08-13 : 23:46:08

Just kinda thinking out loud here...would be interesting if there where C++ kind of structures. I guess they would be table variables, but owned by schemas and implemented as Types.

No idea off the top of my head how they'd be particularly useful, but with a little thought, I bet they would be.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-08-13 : 23:48:57

by the way, seems from the quote from the book you posted, that a type alone still doesn't do a domain justice. but again, if you utilize all of the options available (especially check constraints against the type) then it works

i'm guessing that in practice SQL Server UDT's are good enough, but in theory there's some extra hoops to jump through.

Regarding CLR, I've never used it. Always kind of thought that was the job of the middle tier. Alas, I must admit that Microsoft *and others) has gone to great lengths to obscure the middle tier. All of what we were taught 10, 12, 15 years ago about tier seperation has become very blurred in recent years.

dmilam
Posting Yak Master

185 Posts

Posted - 2010-08-16 : 18:28:44

quote:
http://www.amazon.com/SQL-Relational-Theory-Write-Accurate/dp/0596523068

That's what I was trying to draw on as well.

The point about check constraints is worthwhile. Otherwise, I'll have to bow out here. Good luck!

Kristen
Test

22859 Posts

Posted - 2010-08-17 : 04:46:21

"All of what we were taught 10, 12, 15 years ago about tier seperation has become very blurred in recent years."

If your only tool is a hammer all your problems are nails

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-08-17 : 07:59:50

quote:
Just kinda thinking out loud here...would be interesting if there where C++ kind of structures. I guess they would be table variables, but owned by schemas and implemented as Types.

This is exactly why I like "domain" over "type". It matches Date's definition of "finite set of values" for types but makes it explicit. U.S. Zip (postal) codes are a perfect example: character data of five numeric digits, leading zeros must be preserved, 00000 through 99999. The problem is not all such codes are valid, so putting the valid ones in a table and binding the domain to it make perfect sense to me. In effect, by using that domain, you get an automatic foreign key to the underlying table.

I would hazard a guess that he discusses that in the book and I just haven't read that far yet. Of course, you could simulate it with a rule or check constraint with a giant IN() list.

Or a NOT IN() expression, if that's shorter.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources