Re: Referential integrity

From: Mark C. Stock <mcstockX_at_Xenquery>
Date: Thu, 1 Jun 2006 06:33:51 -0400
Message-ID: <aKCdnRrFe5INX-PZRVn-uA@comcast.com>

"Andreas Moroder" <Andreas.moroder@[nospam]sb-brixen.it> wrote in message news:e5m2kr$ml4$1_at_news.dtag.de...
: Hello,
:
: i have a big table with 25 fields that should be checked for their
: contents. Because I also need the description of this values I should
: build 25 table and activate referential integrity.
:
: When I go on this way I will end with hundreds of very small tables.
:
: Is there another way to guarantee referential integrity ?
:
: I tried with functional indices on one single lookup table, but they can
: not be used a foreign key.
:
: Bye
: Andreas

well, maybe.

if you have a single code table with a 2-part pk, column 1 is the code type, and column 2 is the code, then all your lookup codes code be 2-column FKs, with the first column as the code type (with a default value and a check constraint to enforce one specific code type) and the 2nd column as the actual code

ie:

create table codes (

   type_id number

,  code_id number
,  abbrev  varchar2(12) not null
,  descr   varchar2(30) not null
,  constraint codes_pk primary key (type_id,code_id)
,  constraint codes_abbrev_uk unique (type_id,abbrev)
,  constraint codes_descr_uk  unique (type_id,descr)

)
/

create table personnel (
  personnel_id number constraint personnel_pk primary key

, status_type_id number default 1
, status_code_id number
, constraint chk_status_type_id check (status_type_id = 1)
, constraint personnel_status_fk foreign key (status_type_id,status_code_id)

  references codes
)
/

This results in one extra column per status code per table (rather than one table per status code), but gives you full DRI.

Note that this allows for optional FKs even though CODE_TYPE_ID will be forced to a specific value (if nulls exist in a portion of a multi-column FK, RI is not enforced -- ANSI standard, IIRC)

There should also be a CODE_TYPES tables with an FK from the TYPE_ID column in the CODES table (but not from the *TYPE_ID columns of the other tables -- this is covered by the check constraint)

This type of technique falls into the category of logical implementation vs. conceptual design (or what some would call physical implementation vs logical design) -- I would not recommend modeling the code tables this way in the E/R diagram, but at implementation time, this could be the way the DB is actually built.

Some rambling thoughts on another possible implementation compromise(watch out for stray flack on this one):

Have one code table with codes segregated by ID ranges (I did say compromise, didn't I?) and have single-column FK's referencing this table that also have check constraints enforcing codes within the correct range -- fewer tables, fewer columns, and no issue with running out of codes within a range (use the decimal portion for the actual code, i.e. 1.1, 1.2, 1.3, etc.) but it introduces inteligence into the key value (impure!) and hides the code type in the check constraint -- if the code type is an actual column, then it can be referenced by application code when displaying reference lists of codes, if it is in a check constraint, there's a good change the application code may hard-code the code type -- which would be unlikely to change, but adds some cost to code maintenance and QA (although the table's check constraint would not allow a code in the wrong range to be used, even if the applicaction offers a list of codes in the wrong range.

++mcs Received on Thu Jun 01 2006 - 05:33:51 CDT