Re: Is this bad design ?

"ben brugman" <ben_at_niethier.nl> wrote in message news:<404c9b34$0$281$4d4ebb8e_at_read.news.nl.uu.net>...
> Mother table.
> fields:
> mother_id
> still_valid boolean
> etc_fields
>
> Child table
> fields:
> mother_id
> still_valid boolean
> more_fields
>
>
> The child.still_valid field always contains a true, because only mothers
> withs are still_valid can have children, to enforce this; a relation is
> defined on the fields mother_id and still_valid.
> (A child can not exist without its mother).
>
> Because a field in the child table always has the same value, I think
> there is something wrong. But if this field is taken away a mother might
> become un_valid by another action.
>
> Is this bad design ?
> what is a alternative ?
>
> (As I understand constraints should be implemented in the RDBMS and
> not in the code).
>
> ben brugman

In a DBMS (e.g. Oracle) that doesn't support check/assertion constraints that reference more than one table, you can't do what you would really want to do, which would be something like:

ALTER TABLE mother ADD CONSTRAINT x
CHECK (still_valid OR NOT EXISTS (SELECT * FROM child c WHERE c.mother_id = mother.mother_id));

(Not necessarily correct SQL syntax).

Your inclusion of the still_valid column in the child table is a work-around for that limitation.

An alternative would be to use database triggers, but you would need triggers on both tables and all events that might lead to a violation. Received on Tue Mar 09 2004 - 04:21:53 CST