SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Other Forums
 Other Topics
 Relational Theory Question - Domains
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

robvolk
Most Valuable Yak

USA
15676 Posts

Posted - 08/04/2010 :  17:42:56  Show Profile  Visit robvolk's Homepage  Reply with Quote
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 - 08/12/2010 :  19:39:23  Show Profile  Reply with Quote
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.
Go to Top of Page

robvolk
Most Valuable Yak

USA
15676 Posts

Posted - 08/12/2010 :  23:21:52  Show Profile  Visit robvolk's Homepage  Reply with Quote
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.
Go to Top of Page

dmilam
Posting Yak Master

185 Posts

Posted - 08/13/2010 :  14:11:00  Show Profile  Reply with Quote
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.
Go to Top of Page

robvolk
Most Valuable Yak

USA
15676 Posts

Posted - 08/13/2010 :  15:48:40  Show Profile  Visit robvolk's Homepage  Reply with Quote
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.
Go to Top of Page

russell
Pyro-ma-ni-yak

USA
5072 Posts

Posted - 08/13/2010 :  16:54:06  Show Profile  Visit russell's Homepage  Reply with Quote
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

Edited by - russell on 08/13/2010 16:55:12
Go to Top of Page

robvolk
Most Valuable Yak

USA
15676 Posts

Posted - 08/13/2010 :  17:32:23  Show Profile  Visit robvolk's Homepage  Reply with Quote
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.
Go to Top of Page

russell
Pyro-ma-ni-yak

USA
5072 Posts

Posted - 08/13/2010 :  23:46:08  Show Profile  Visit russell's Homepage  Reply with 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.

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

russell
Pyro-ma-ni-yak

USA
5072 Posts

Posted - 08/13/2010 :  23:48:57  Show Profile  Visit russell's Homepage  Reply with Quote
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.

Edited by - russell on 08/13/2010 23:52:25
Go to Top of Page

dmilam
Posting Yak Master

185 Posts

Posted - 08/16/2010 :  18:28:44  Show Profile  Reply with Quote
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!
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 08/17/2010 :  04:46:21  Show Profile  Reply with Quote
"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
Go to Top of Page

robvolk
Most Valuable Yak

USA
15676 Posts

Posted - 08/17/2010 :  07:59:50  Show Profile  Visit robvolk's Homepage  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.17 seconds. Powered By: Snitz Forums 2000