Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Looking for a way to implement SQL-92 Domain

Re: Looking for a way to implement SQL-92 Domain

From: Hans Forbrich <forbrich_at_telusplanet.net>
Date: Tue, 25 Mar 2003 03:34:10 GMT
Message-ID: <3E7FCD63.4F5EF16B@telusplanet.net>


Galen Boyer wrote:

> You can't guarantee that the lookup field entered is contained within
> the correct domain, just that it exists within the entire domain of
> lookups. The seperate lookup tables assure that you have the correct
> domain as well, not just _any_ lookup value.
>

Actually, you can merge all lookup tables and keep referential integrity. It's ugly but workable!. You need to add a ref_type or domain_type column, never filled, with a default and possibly a check constraint, that drives to the correct domain. The foreign key goes against the twinned 'domain' and 'value' columns. Works because the twinned columns are the primary key.

Some of the reasons for doing this, at least some I used 8 years ago

- performance (especially now with ability to pin tables)
- single 'form' to handle all domains.
- ability to add domains as required (Create 1 domain that contains the
list of domains. Do not enforce integrity on this)
- add a 'description' column and get consolidated documentation
- simpler maintenance, especially when there are 200+ domains
- single piece of look-up code
- lookup code can be automatically generated,
- it's consistent

Biggest reason for not doing this - all that wasted disk space (Actually that could be a bogus argument - what's the space required for 100 indexed tables with 2-10 rows each & avg. row length of 50 bytes?)

/Hans Received on Mon Mar 24 2003 - 21:34:10 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US