1NF question: maybe dumb but needs an answer

[jimbov]

Greetings...

i have delved into normalisation theory and i would like to now one thing.....

when i remove fields that appear to be a repeating group i put them under a new table with a new PK that consists of an ID and the PK of the first table...a composite PK...hope i have get this right......

i know that by using a composite-2 fields PK it is like creating the basis for a M:N relationship....hope i get this one right too....

all these take place when you know business rules and apply them to logical model logic.....what happens when you realize that i.e. you won't by having a m:n relationship...what happens to 1NF then....?? you just create a new table with the repeating fields and insted of using a 2-fields PK you use 1 field PK and a foreign key - PK of the originating table?

i know the question might sound dumb but i need to know.... plus i have bought several books stating that 1NF ALWAYS ends up in the creation of another entity with a 2-column composite PK....

i have attached an example of mine with an E-R....hope it's ok....

hope you have time to have a look at it and post comments....

thank you all in advance....!

[asterix]

what happens when you realize that i.e. you won't by having a m:n relationship...what happens to 1NF then....?? you just create a new table with the repeating fields and insted of using a 2-fields PK you use 1 field PK and a foreign key - PK of the originating table?

Well, thats almost right.

You create a new table. You remove the redundancy in the repeating fields, so you have a new table of unique values, and assign each record a PK. You reference your original table with an FK -> PK in the new table.

This is exactly the relationship you should have between INVOICE_ITEM and ITEM: 1 : N INVOICE_ITEM

Your ERD is wrong, BTW. your ITEM table should be normalized, with each item appearing only once and the PK (item_id) also being unique.

- Richard

[jimbov]

Well, thats almost right.

Your ERD is wrong, BTW. your ITEM table should be normalized, with each item appearing only once and the PK (item_id) also being unique.

- Richard

First, let me thank you for your reply.....

oopsie daizie....i forgot to remove the duplicate values for the ITEM table.....i have re-attached the example...

Pleazzzz have a look at it....hope that it is alright now....

One last question...:

ERD precedes normalization, is that correct???

so, ERD (logical model) -> Normalisation -> Physical model.....???

to state a M:N re

[jimbov]

oopsx2....in a hurry to send the thread and didn'complete it...

anyway, i have included a 2nd version of the ERD in which the intermediate table for the M:N relationship ITEM M:N INVOICE is replaced by a relationship with attributes...i have read about it in a book of mine...

from your experience, can the above hold true....?

[asterix]

Everything is fine now. People rarely use ERDs the way you did, but it is correct.

Your 3NF is excellent.

You have what is known as a defining relationship to INVOICE_ITEM created by the FKs to Invoice and Item, using both FKs in this table will build your PK.

Some people don't much like defining relationships, I'm one of those people. I would include a redundant PK -auto_id - on the Invoice_ITEM table, but still define a unique index over the two FKs to retain referential integrity. The redundant PK has absolutely no meaning and is just a bookmark to the row, which is way I prefer it.

People sometimes do things the way you did: ERD, 2NF -> 3NF, but in practice there are many reasons to leave the tables unnormalized (speed). ERDs are slowly going out of fashion due to object-oriented programming techniques, many people just use UML nowadays, since it offers everything and more than ERDs.

The normalization process splits attributes into new entities. This is entirely done in the logical model. The physical model may, for example, have completely different entities, and use views to give a normalized or denormalized way of accessing the data.

HTH, Richard