Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Looking for a way to implement SQL-92 Domain
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 thelist 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