[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