Posted by Gert-Jan Strik on 02/24/07 20:47

Emin wrote:
>
> Dear Experts,
>
> When I use a single table I can easily use constraints to enforce my
> business logic, but what do I do when I normalize a single table into
> multiple tables.
>
> For example, imagine that my initial table has the columns ID, Name,
> Salary with the constraint that Salary is not NULL. Now imagine that
> I break this into two tables, one with ID and Name and another with ID
> and Salary. I would like to have a constraint that prevents the
> creation of a row with (ID,Name) in the first table unless a
> corresponding row in the second table is also created.
>
> I can enforce this logic with triggers, but it looks ugly and is
> fairly brittle. Is there a better way or is this the dark side of
> normalization?
>
> Thanks.

Your example doesn't make sense. Following the normalization rules, you
would not split this initial table, because there is no reason to split
it. Why do you want to split this table?

But even if you had an example: it is usually not necessary to go beyond
the 3rd normal form when modelling for an RDBMS. It just add a lot of
overhead and complexity to your queries.

Gert-Jan

[Back to original message]