Re: Which normal form is this violating?

Roger Redford (dba_222_at_yahoo.com) wrote:
: The simple rules are:

: A 1:1 is in one table.
: 1:M is in two tables.
: M:N is in three tables.

: If we say that the example is perfectly normalized,
: why stop with the one field? We could then overapply
: 1NF rule to the other fields (1NF is take out repeating
: groups, and put them into another table, creating a 1:m.
: Most people understand this, but stop there.)

: Say we have the employee table with:

: EMP
: emp_id (pk)
: name
: address
: state
: zip
: phone
: start_date

: If you then split the one table into 6 tables, each with
: the PK of: emp_id,
: and one table each for:
: name
: address
: state
: zip
: phone
: start_date (etc)

: you now have 6 tables, instead of one. Indicating six
: one to many relationships. When in fact, it is all just a
: 1:1 relationship.

In fact, it may or may not be all a 1:1 relationship. That depends on the data. In your example, you could _require_ an employee to have exactly one recorded address, phone, and start date, in which case it would be 1 to 1. However, a different system might allow an employee to have multiple addreses (rare but possible), multiple phone numbers (quite common) and multiple start dates (odd, but it happens if you have employees who work part time in several different positions).

If you allow your system to also handle things such as members of a family that reside at the same address, then it's quite reasonable to end up with four tables, (name) (address,state,zip) (phone) (start_date).

In the general case you might need one table for every attribute associated with a key.

: Perfectly normalized would clearly be in the one table, not six
: or more. But what normal form does this violate?

As I said, whether this is normalized depends on the allowed data of the relationships, not the table structure.

: Does anyone know?

It may violate both 3rd normal and Boyce-Codd, but again that depends on the allowed data. Received on Thu Apr 25 2002 - 16:36:05 CDT