[Pdx-pm] Eliminating circular table relations.

[benh]

1) is this jifty or just sql?
2) wouldn't it just be something like:

CREATE TABLE account (
   id                  INTEGER    PRIMARY KEY,
   name                VARCHAR,
   address             VARCHAR,
   phone               VARCHAR
   sub_type_id        INTEGER,
);

CREATE UNIQUE INDEX account_to_sub_account (id,sub_type_id) ;

# 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')
);


though I dont know if you can have a paired unique key that invovles
the primary.... humm.





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


-- 
benh~