[Pdx-pm] Eliminating circular table relations.

Michael G Schwern schwern at gmail.com
Thu Feb 1 06:29:55 PST 2007


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

That seems like its pushing the data integrity up out of the database and into the application code. :(


More information about the Pdx-pm-list mailing list