[Pdx-pm] Eliminating circular table relations.

[Adriano Ferreira]

On 1/31/07, Michael G Schwern <schwern at gmail.com> wrote:
> 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.

That means one additional common attribute may be some kind of
account_type_id. This account_type_id in your case should live in a
discriminator table with two rows: one for meat accounts and another
for mushroom accounts. As this attribute will be required, there is no
way to create an account that is other than meat or mushroom (unless
you add a new type -- but you should know what you're doing in this
case).

The remaining logic of your application should guarantee that for an
account marked as meat there is a row in meat_account and the same
with respect to mushroom_accounts. This is the ordinary approach to
such issues in relational models.

>
> 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.
> _______________________________________________
> Pdx-pm-list mailing list
> Pdx-pm-list at pm.org
> http://mail.pm.org/mailman/listinfo/pdx-pm-list
>