[Pdx-pm] Eliminating circular table relations.

[Michael G Schwern]

This is a SQL question so everyone else can safely let their eyes glaze over.

Let's say I'm storing account information in a database.  And let's say there are two different types of accounts (how about "meat" and "mushroom"), but they share some common characteristics.  My impulse is to put the common information in one table and have another table for each sub type.

CREATE TABLE account (
    id                  INTEGER     PRIMARY KEY,

    name                VARCHAR,
    address             VARCHAR,
    phone               VARCHAR
);

# sub-type
CREATE TABLE meat_account (
    id                  INTEGER     PRIMARY KEY,
    account_id          INTEGER     REFERENCES account (id),
    
    drippings           BOOLEAN,
    gravy               ENUM("white", "brown", "red")
);

# sub-type
CREATE TABLE mushroom_account (
    id                  INTEGER,
    account_id          INTEGER     REFERENCES account (id),
    
    type                ENUM('shitaki', 'crimini')
);

While this ensures each sub-type row has an account row, it allows an account row to exist without a sub-type.  That's not allowed by the business rules.  Each account has one sub-type row and for each sub-type there is one account.

I could add in a constraint to the account table to check that a meat or mushroom account exists, but now I have a circular dependency.  That's bad.  It means in order to create or delete an account I have to defer constraint checking while I create (or delete) both the account row and the associated sub-type row.  To add to the problem, this is MySQL 5.0 which does not have proper constraint deferral. [1]

There's got to be a better way to structure this.  Ideas?


[1] MySQL can turn off constraints but it does not re-check them when you turn them back on.