constraints across normalized tables

[Emin]

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.

[Erland Sommarskog]

Re: constraints across normalized tables

Emin (emin.shopper@g mail.com) writes:

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?

I wouldn't call that particular example normalistion, but rather
vertical partitioning. :-)

If you have

CREATE TABLE leftside (id int NOT NULL PRIMARY KEY,
name varchar(34) NOT NULL)
CREATE TABLE rightside (id int NOT NULL PRIMARY KEY,
salary int NOT NULL)

You can use a foreign-key constraint to ensure that an id is not
inserted into leftside, if it's not also in rightside.

However, there is no way that you can ensure that there are rows in
both table, at least not with useful data. Not with triggers, not
with constraints, since there are not any commit-time versions of
the same.

The best you can do is to have a trigger to cascade inserts with dummy
values into the other table.

More generally, cross-table checks you have to do with triggers,
with the exception of foriegn keys. (OK, you can use user-defined
functions that you call from you CHECK constraints, but that can
be very costly performancewise .)



--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[--CELKO--]

Re: constraints across normalized tables

>When I use a single table I use constraints to enforce my business logic, but what do I do when I normalize a single table into multip can easilyle tables. <<

The same thing -- use constraints to enforce business logic.

>For example, imagine that my initial table has the columns ID, Name, Salary with the constraint that Salary is not NULL. <<

Your data element names are all wrong. There is no such magical
creature as a Universal ID; this is OO or file system record numbers.
Name of what? Employee, maybe? Salary_type? Salary_amt? In fact, we
have no ideas what the name of this table is!!

>>Now imagine that I break this into two tables, one with ID and Name and another with ID and Salary. <<

We fire you for not having taken a data modeling course and knowing
about attribute splitting. The rule is that all the attributes of an
entity stay in one and only one table. Can I assume that this is
Personnel (emp_id, emp_name, salary_amt) and that the three are
required attributes?

>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. <<

You do not need that if you keep your **already normalized** table.

I am guessing that you are really trying to ask about DRI actions, but
don't have enough background to know the concepts of normalization and
how they work together.

[Erland Sommarskog]

Re: constraints across normalized tables

--CELKO-- (jcelko212@eart hlink.net) writes:

>>For example, imagine that my initial table has the columns ID, Name,

Salary with the constraint that Salary is not NULL. <<

>
Your data element names are all wrong. There is no such magical
creature as a Universal ID; this is OO or file system record numbers.
Name of what? Employee, maybe? Salary_type? Salary_amt? In fact, we
have no ideas what the name of this table is!!

Joe, in your endeavour to spit on as many SQL users as posssible, can't
you just be a little bit discriminant? Emin's question was apparently of
a generic nature, and his table was just an exmaple. For a table that
is just to be used in an example, and nothing else I think "id" is a
perfect name. There is no reason to call it CheeseID, AppleID, or
WhateverID; that would only distract attention from what the example
is intended to discuss.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[--CELKO--]

Re: constraints across normalized tables

>Emin's question was apparently of a generic nature, and his table was just an example. <<

Because good specs are important; because there is no such thing as a
"generic table"; because we need to know if the FDs would allow that
table to be split like he was trying to do.

> There is no reason to call it CheeseID, AppleID, or WhateverID; that would only distract attention from what the example is intended to discuss. <<

So, how do you find the FDs and make a rational decision about this
nameless table? If he had given us real-world names, then we could
guess. What we had was (id -name; id -salary) or (id -name;
name -salary) or ((name,salary)-id) or etc.

If we keep giving these newbies kludges instead of showing them that
their questions are not properly formed, then they are going think
what they are doing is just fine and never learn RDBMS.

>
--
Erland Sommarskog, SQL Server MVP, esq...@sommarsk og.se
>
Books Online for SQL Server 2005 athttp://www.microsoft.c om/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.c om/sql/prodinfo/previousversion s/books.mspx

[Gert-Jan Strik]

Re: constraints across normalized tables

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

[Ed Murphy]

Re: constraints across normalized tables

--CELKO-- wrote:

>>Emin's question was apparently of a generic nature, and his table was just an example. <<

>
Because good specs are important; because there is no such thing as a
"generic table"; because we need to know if the FDs would allow that
table to be split like he was trying to do.
>

>> There is no reason to call it CheeseID, AppleID, or WhateverID; that would only distract attention from what the example is intended to discuss. <<

>
So, how do you find the FDs and make a rational decision about this
nameless table? If he had given us real-world names, then we could
guess. What we had was (id -name; id -salary) or (id -name;
name -salary) or ((name,salary)-id) or etc.
>
If we keep giving these newbies kludges instead of showing them that
their questions are not properly formed, then they are going think
what they are doing is just fine and never learn RDBMS.

You need a good cop and a bad cop. Except in Alpha Complex, where
the positions have been combined due to budgetary restraints. :)