Code tables, conditional foreign keys?

[Benjamin Smith <lists@xxxxxxxxxxxxxxxxxx>]

I have some questions about the best way to best use foreign keys in complex
schemas. It's becoming cumbersome to manage a large set of foreign keys - is
there a better way?

// FOUNDATIONAL //

Let's say that you want to keep addresses, and one of the values that you need
to keep is the state. So you have two tables defined:

create table states
( state varchar unique);
create table customers
(... state varchar not null references states(state), ...);

If you want to be a bit more "pure", you might do it like this:

create table states
(id serial primary key, state varchar(2), description varchar);
create table customers
(... states_id integer not null references states(id), ...);

So far, so good. But when you have a large number of fields with foreign key
references, you end up with a bazillion reference tables, all with very
similar layouts. EG:

create table customer_types
(id serial primary key, title varchar(4), description varchar);
create table customer_taxcode
(id serial primary key, title varchar(4), description varchar);
...
create table customers
(...
customer_types_id integer not null references customer_types(id),
customer_taxcode_id integer not null references customer_taxcode(id),
...);

Getting the appropriate code tables from all these different tables becomes
cumbersome, just because there are SO MANY tables to get these values from.

So the next idea is to create a master set of code tables and foreign key to
there, but this has its own set of problems EG:

create table codetables
(
id serial primary key,
table varchar unique not null
);
create table codevalues
(
id serial primary key,
codetables_id integer not null references codetables(id),
value varchar not null,
unique(codetables_id, value)
);
create table customers
(
customer_types_id integer not null references codevalues(id),
customer_taxcode_id integer references codevalues(id),
)

How do you know that taxcode_id references the correct set of code values? You
could use a dual foreign key, but then you have to have a field for each and
every codetable you reference, eg:

insert into codetables(33, 'customertypes');
insert into codevalues(codetables_id, value) values (33, 'Gubbmint');
create table customers
(
customer_types_id integer not null,
customer_taxcode_id integer,
custtypes not null default 33, -- the codetables.id for customer types
taxcodes not null default 34, -- the codetables.id for taxcodes
foreign key (custtypes, customer_types_id)
references codevalues(codetables_id, id),
foreign key (taxcodes, customer_taxcode_id)
references codevalues(codetables_id, id)
);

This also becomes cumbersome. Is there a better way? Is there some way to do
this not covered in the docs? What would be ideal is to treat the reference
something like a join - might be something like:

....
create table customers
(
...
customer_taxcode_id integer not null references codevalues(id) ON
codevalues.codetables_id = 33,
...
)
...

Thanks!

-Ben

--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.