[Pdx-pm] Eliminating circular table relations.
Adriano Ferreira
a.r.ferreira at gmail.com
Thu Feb 1 02:57:34 PST 2007
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.
More information about the Pdx-pm-list
mailing list