Re: Is this bad design ?

"Fabian Buettner" <green_at_tzi.de> wrote in message news:404DC817.2090804_at_tzi.de...
> If only valid mothers can have children, you could partition your Mother
> table as follows:
>
> 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)
>
> This way you can ensure that only valid mothers are referenced by
> children. Also (if referential integrity is available), a mother can not
> become invalid (i.e., delete entry from ValidMother) when there are
> still children referencing it.

But then the application has to be changed. (The databasemodel changes). And changing an application because of a constraint was / is (by some) considered as not preferable.
(For selections a view would be possible, but for inserts/updates/deletes this would not work).

(At the moment we use a software lock on the mother to prevent a change in any of the mothers children (grandchildren). The software lock is build by using a changenumber, and anybody altering the mother or children (or grandchildren) has to use the software lock on the mother.
But I am not satisfied with this solution either.)

ben brugman Received on Tue Mar 09 2004 - 15:49:22 CET