Re: Which normal form is this violating?

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.

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

Does anyone know?

Thanks

"dario" <drga59_at_hotmail.com> wrote in message news:<fdPx8.48272$o66.149595_at_news-server.bigpond.net.au>...
> It does not violate any normal form, it is perfectly normalized, but it
> violates common sense.
>
> "Roger Redford" <dba_222_at_yahoo.com> wrote in message
> news:a8c29269.0204242031.9d9964f_at_posting.google.com...
> > Hello DB Design experts,
> >
> > I'm having the usual disputes about database design issues.
> >
> > The information that my coworkers have is say, x and y.
> > It has a one to one relationship. Therefore, it
> > goes into one and the same table.
> >
> > Table_A
> > Fieldx (pk)
> > Fieldy
> >
> >
> > However, they are arguing that it goes into another table.
> >
> > Table_A
> > Fieldx (pk)
> >
> > Table_B
> > Fieldx (pk)
> > Fieldy (not null)
> >
> > (Actually, thye have "designed" a number of strange tables,
> > and then put views on top of them, to come back to the same one to
> > one relationship. Very strange and complex. )
> >
> > What normal form does this violate? It isn't 1st,
> > 2nd, or 3rd. Boyce-Codd maybe? The crazy thing about
> > the design texts, is that they rarely cover mistakes
> > in design. This is a common one.
> >
> > Thanks
Received on Thu Apr 25 2002 - 16:56:34 CEST