Data normalization

By v_vishram ·
Does anyone have any tips to normalise some data for tables in Access.

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

Collapse -

Do you have some example data?

by LocoLobo In reply to Data normalization

You could try the table analyzer,

tools - analyze - tables

Sometimes it doesn't always work the way you think it should.

Collapse -

It's mainly common sense

by Tony Hopkinson In reply to Data normalization

The first rule is each row must be unique.
Now that could mean all the columns in the table but that would be silly if there's a lot of them.
So to get to the first normal form identify the mininum number of column values you need to identify any row in the table accurately.

This could be very simple if say it was the customer table and you have a customer number.

2 Normal Form is dependency
Say you had
Customer No, Customer Name, Customer Phone, Order Number, Order Date....

Hopefully you get more than one order per csutomer, so their name will be in there once for every order they have
Have a Customers Table with Number Name and Phone
And an Orders Table
with customerno, order no, order date....
And you just got to second normal form well as long as there aren't any more dependancies in there.

Not as as arcane and complex as some would have you believe is it?

Third Normal form
is partial dependancies
Lets say customer phone in our order table was that of the person who made the order.
If you get orders from more than one contact of a customer
CustomerPhone doesn't uniquely identify an order and Customer doesn't uniquely identify phone, In fact phonenumber doesn't uniquely identify the person on the other end of it.
What if two conatcts shared the same phone, who says they are for the same customer?

So now you end up with
ContactNo,CustomerNo, PhoneNo, ContactName
OrderNo, CustomerNo, ContactID, OrderDate...

Ok that was a bit harder, if you want to bleed from the eyes look up 6th normal form.

Essentially all normalisation does is minimise the number of the times we store the same piece of data that doesn't identify a unique item.

There are 4th, 5th and even 6th normal forms. each one gives successively less benefit.

Many stop at 2nd normal form, some will go to 3rd, after that you need a lot of data to make the increased complexity worth while.

Oh and please remember that two John Smiths is not necessarily the same person twice.

One thing that isn't often apparent is that for a table to be in 3rd normal form, it also must be in 2nd and 1st.

So make sure of 1st, then 2nd and for me personally 3rd, then see where you are at.

Can't say I've ever bothered with a tool for this sort of thing, the bulk of it is blindingly obvious when you get the gist of it. Besides a tool could only guess by column name and content, in any non-trivial database that will make a complete stuff of it.

Related Discussions

Related Forums