Re: Code tables, conditional foreign keys?

On 26/05/09 20:48, Benjamin Smith wrote:
> "A deep unwavering belief is a sure sign that you're missing
> something." -- Unknown
>
> I had no intention of sparking an ideological discussion.

I know, my apologies for going off-topic. I just had a deja-vu when I
saw Celko's article about EAV disasters mentioned again in a very
similar context.

> So.... back to the first question: is there a way to have a
> conditional foreign key?

I can only suggest what we've done in this situation. We had a table
setup similar to yours (tables like "customer" referencing many small
sets like customer type or education level, with the exact same
structure). All of the small sets were combined in one table (which is
what reminded people of EAV design). Using your original example ...

create table codetables
        (
        id serial primary key,
        name 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),
        )

... you need to make sure that customer_types_id references the correct
codetable set within codevalues. To do this, we added CHECK constraints
in our tables:

CREATE TABLE customer (
    ...
    customer_type_id  INT    NOT NULL,

    -- this is the standard FK to codevalues
    CONSTRAINT fk_customer_type_id
        FOREIGN KEY (customer_type_id)
        REFERENCES codevalues (id),

    -- this makes sure that the correct set is referenced
    CONSTRAINT check_customer_type
        CHECK (belongs_to_codetable('customer_type', customer_type_id))
);

CREATE FUNCTION belongs_to_codetable (VARCHAR(255), INT)
RETURNS BOOLEAN
AS '
    SELECT EXISTS (
        SELECT 1
          FROM codetables ct
          JOIN codevalues cv
            ON cv.codetables_id = ct.id
           AND ct.name = $1
           AND cv.id = $2
    )
' LANGUAGE 'SQL';

We used different names, so this is untested, but in principle it should
do what you require.

Whether this is a good design or not... I'm still not sure. Joe Celko
would grill me for doing something like this.


  - Conrad