Re: Which normal form is this violating?

dba_222_at_yahoo.com (Roger Redford) wrote in message news:<a8c29269.0204250656.633bc488_at_posting.google.com>...
> 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.)

Sounds good so far.

>
> Say we have the employee table with:
>
> EMP
> emp_id (pk)

Why do you instantly define this emp_id as the PK? If there is a businees reason for it, fine, but there is no database design reason for it. So start with the other inforation:
> name
> address
> state
> zip
> phone
> start_date

Identify your primary key first. Here it is likely the combination of name and start date. In the USA you might also consider added Social Security Number and maybe using that. (Picking an arbitrary emp_id as the PK can lead to problems if you are not VERY carful. One of the problems is the split tables you describe.)
>
>
> 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

HTH
  Ed Received on Thu Apr 25 2002 - 16:27:30 CDT