Re: Is this bad design ?

From: Fabian Buettner <green_at_tzi.de>
Date: Thu, 11 Mar 2004 11:16:02 +0100
Message-ID: <40503C62.2020700@tzi.de>

If you refer to the solution I proposed:

Change a mother (mid) to invalid:
DELETE FROM ValidMother WHERE mother_id = mid -- delete will fail if this mother has children (as intended)

Change a mother (mid) to valid:
INSERT INTO ValidMother(mother_id) VALUES (mid) WHERE NOT EXISTS( SELECT * FROM ValidMother WHERE mother_id = mid) -- the where not exists part can be dropped if you ensure that mid was -- invalid before

Tables where:
Mother( mother_id primary key, etc_fields ) -- all mothers

ValidMother( mother_id primary key + foreign key ref Mother(mother_id)) -- "valid mother" ids

Child( mother_id foreign key ref. ValidMother(mother_id), more_fields)

Fabian

ben brugman wrote:

>>>But then the application has to be changed.
>>
>>No, it doesn't. A simple view serves the application well.
>>
>>

>
>
> If a view is used (I assume a union over both tables),
> how does the application change the data from
> valid to non-valid and vice versa ?
>
> ben
>
>
Received on Thu Mar 11 2004 - 04:16:02 CST