[Pdx-pm] Eliminating circular table relations.

[Adriano Ferreira]

On 1/31/07, Michael G Schwern <schwern at gmail.com> wrote:
> Adriano Ferreira wrote:
> >> 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).
>
> Could you write out that discriminator table long hand?  I'm a little hazy on how it would work and I'm having trouble finding examples.
>

CREATE TABLE account_type (
   account_type_id INTEGER PRIMARY KEY,
   account_type VARCHAR
);

INSERT INTO account_type
           (account_type_id, account_type)
VALUES ( 1, 'meat');

INSERT INTO account_type
           (account_type_id, account_type)
VALUES ( 2, 'mushroom');

CREATE TABLE account (
  id                  INTEGER     PRIMARY KEY,
  account_type_id INTEGER REFERENCES account_type(account_type_id),

  name                VARCHAR,
  address             VARCHAR,
  phone               VARCHAR
);

Every time you create an account, you must specify which account type
you want from those available in the table account_type. In this case,
these are two. The business logic would create a meat account with one
row in account and other in meat_account, or a mushroom account with
one row in account and other in mushroom_account to retain integrity.
If there is any error, rollback both rows so the model is not left
incomplete.

When you delete an account, you could choose a DELETE ON CASCADE or do
it by hand at your application code as well.