Re: Seeking Equivalent of InterBase Domain

From: Frank van Bortel <frank.van.bortel_at_gmail.com>
Date: Sat, 20 Jan 2007 11:26:16 +0100
Message-ID: <eosqo8$4g1$1@news6.zwoll1.ov.home.nl>

EdStevens schreef:

> MRCarver wrote:

>> In past InterBase databases that I have worked with, since there is no
>> Boolean data type, I have used a Domain called BOOLEAN_DOMAIN, defined
>> in DDL as follows:
>>
>> create domain BOOLEAN_DOMAIN as CHAR(1) default 'N' NOT NULL check
>> ((VALUE = 'N') or (VALUE = 'Y'));
>>
>> then in my create table statements it becomes very simple:
>>
>> create table MY_TABLE (
>> TABLE_KEY KEY_DOMAIN NOT NULL,
>> HAS_BOOL BOOLEAN_DOMAIN,
>> etc.
>>
>> So far the only thing I have seen to do in Oracle is to create the
>> boolean column as Char(1) and then put a check constraint below my
>> primary key constraint definition.
>>
>> I have explored using a "Type", but it seems that this construct goes
>> way beyond what a domain is designed for in InterBase. There is also
>> some reference in the literature to sub-types. Are sub-types related?
>>
>> Any suggestions?
>>
>> Monte

> 
> Well, first off .. InterBase <> Oracle.  Don't try to reverse engineer
> to work like something else.
> 
> Second, maybe I'm missing something, but it looks like you are wanting
> a boolean data type for a primary key column????  That would mean you
> could only ever have two rows in the table.  I must be missing
> something.
> 

The closest to a domain Oracle has is a table (yes I know - sounds obvious), a lookup table to be precise. Nothing in Oracle that prevents you to define a table with a single column and just one row, and use that as a foreign key reference table.
One row in the lookup table, and a NULLable column on the base table, and you have a NULL/value domain. Make the base column NOT NULL, and provide two rows in the lookup table, and you have a value1/value2 (boolean?!?) lookup.

In case of a boolean domain, that is the entry should be either value1 or value2, I'd opt for a check constraint on the base table, not for a foreign key and lookup table. Anything over, say, 5 values, I'd probably go for a lookup table, especially when "domain values are almost always constant". Meaning they change.
In case of deletion/alterations, tables are harder to maintain, if not carefully designed (use of a valid_until date!) than check constraints.
In case of additions only, the table construct is by far the easiest!

As usual - it depends :). On your business needs. I miss the original thread, so this is more of a relply to Monte than to Ed, and I hope this clarifies some.

-- 
Regards,
Frank van Bortel

Top-posting is one way to shut me up...

Received on Sat Jan 20 2007 - 04:26:16 CST