Normalisation

[blander]

Hi,

I am trying to normalize some data into some tables and am struggling a little.

The data contains some normal client data such as:

First Name
Last Name
Address

And also data such as:

income range - 20-50, 50-80, 80-100 etc
acquisition method: Radio, television, mailing etc

Each record will have one of the above e.g.:

John
Smith
23 Smith St
50-80
Radio

My question is, how do I input this data into tables so that it is in 3NF.

If i enter this data straight into a spreadsheet the fields income and acquisition method will have repeating fields.

I will eventually be using this data to create a form so someone can enter the the income and acquisition method via a drop down box for every record.

Thanks in advance!

Ben

 

[gemma-the-husky]

your data is normalised - repeating groups really mean there are common sets of data within the whole data set - this would only apply if the same salary group was ALWAYS associated with the same acquisition method - but this clearly isnt the case.

However, you might want to actually store a numeric value representing the income group, and the acquisition method.

(eg 1 = 20-50,
2 = 50-80,
3 = 80-100 etc

normally you would enter this data by selecting from a combo box, say - so you see and pick the text description, but store the associated number.

you can either populate the combo box by using a lookup table, or directly with fixed values - depends how flexible you need it.

The benefit of this is
a) efficiency
b) you can change the text description without affecting the data storage.

-----------
One area where there MAY be (hidden) repeating group is within the address - although most people don't bother and just store addresses, really big systems may actually deconstruct addresses into constituent elements. If you want to see this in action, get a sample of the post offices address/post code search mechanism.

And some people might store addresses in a separate table, and store a lookup reference to the address in the main table.