How much do I need to normalize?

[CarysW]

I'm finally getting to grips with this normalization business (!).

But what I need to know is exactly how far down do I need to split my main table. Below is all of the field names with a brief description of how many entries there are likely to be for each etc etc. As I'm completely obviously rubbish at this I'd really love it if someone could help me out with a shove in the right direction as it is probably really simple when you know how.

Key - Primary Key, Autonumber, unique to each entry
Search Code - repeated for each branch of a company, is an identifier for my company, will be used for search but mainly just displayed in results
Debtor number - same Search Code, the two are kept for historical reasons(!)
Currency - will either be GBP or EUR
Pricelist - there are 4 different types of pricelist, stores can only have 1 type.
Exact Code - there are 12 codes, only used for display purposes in results
Exact Category - directly correspond to Exact code, will be used for searches and will be shown.
Business Name - Not unique, accounts have multiple branches, some accounts have the same name (!)
Trading As - as Business name, although some accounts have the town name in their 'Trading As' name.
Rep - there are 10 reps, each account has 1 of these.
Office Rep - there are 4 of theses, same as Rep.
Branch/HQ - will either be branch or HQ
Address 1 }
Address 2 }any of these 3 could be blank or all could be filled in for each.
Address 3 }
Town - a couple of hundred of these, will be used for search and display
Postcode - same as Address 1,2,3
County - there are about 100 of these, used for search and display
Tel - just display
Fax - just display
Email - just display
Web - just display
Notes - just display
Region - about 25, mainly used for search but may also be displayed
Country - as Region
Military - yes/no field, will be used for search
Watersports - yes/no field, will be used for search
Store/site - yes/no field, will be used for search and display
Online Shop - yes/no field, will be used for search and display
Mail order - yes/no field, will be used for search and display
Web Stockist List - yes/no field, will be used for search and display
Emailout - yes/no field, will be used for search and display

If anyone could help I would appreciate it a lot.

Thanks
Carys

 

[georgedwilkinson]

All the way.

Put things that are about a certain entity in its own entity. I've commented on this multiple times on this forum. For instance, here: http://www.access-programmers.co.uk/forums/showthread.php?t=189081

The purpose of normalization is not as an academic exercise; it is to remove data anomalies.

 

[CarysW]

Thanks George.

I've really gone back to the beginning with this - there's no point patching it, it needs rebuilding. :-(

 

[gemma-the-husky]

it doesnt look like there is too much wrong with that table
all this needs is some associated lookup tables - and where you say 10 items/ or 4 choices this identifies an expectation of an associated lookup table

but you say this is your main table - so lets say this is your customer/client table

so what you now need is tables for your orders / order lines/ deliveries and so on.

The price list example could be quite complex. You need to know which items are in that price list - decide whether / how to store the price history etc, as the prices change and so on.

Then there are practical issues - what if a customer wants to buy a product that isnt in his price list. Can he? How do you deal with that? What price do you charge him? These are the areas that you need to think carefully about. How do you maintain price lists - change prices/add new products into them, and so forth.

 

[CarysW]

it doesnt look like there is too much wrong with that table
all this needs is some associated lookup tables - and where you say 10 items/ or 4 choices this identifies an expectation of an associated lookup table

but you say this is your main table - so lets say this is your customer/client table

so what you now need is tables for your orders / order lines/ deliveries and so on.

The price list example could be quite complex. You need to know which items are in that price list - decide whether / how to store the price history etc, as the prices change and so on.

Then there are practical issues - what if a customer wants to buy a product that isnt in his price list. Can he? How do you deal with that? What price do you charge him? These are the areas that you need to think carefully about. How do you maintain price lists - change prices/add new products into them, and so forth.

Ah, you see there's the thing. The DB wont contain customer orders or the items we sell. It's simply a container for names, addresses etc. It will be used for stockist details and mailouts. Nothing else. The reason it needs normalizing (I'm guessing) is because I need quite a search facility.

 

[gemma-the-husky]

OK, but this isnt really normalisation, its more an exercise in thinking about lookup tables

take a couple of your fields

REP - now you say there are a number of values, and I assume these are names.
now you could store these as text. But if you do this, you might mis-spell the reps name, in diffrent rows, so access thinks you have more than the correct number of reps.

so instead, store the reps names in another table, and when you select a rep you limit the choices to the names in that table. This goes a step further normally - the reps table will normally have an ID number AND a name - and you store the ID number in your customer table - because
a) it is more efficient, and
b) it means you can change the name any time - becasue all the customer table knows is that a given customer is matched to rep 12

But you are STILL storing a rep value in the customer table - the customer table hasnt been deconstructed into multiple table (if you see what I mean)

This same argument applies to pricelist, exactcode etc

============
now take

OFFICE REP - this is interesting. If an officerep serves a particular group of reps, always, then you dont need to store the officerep in the customer table. if you store the officerep in the rep table, then given any customer, you can find the rep, and then find the officerep for that customer.

if you store both in the customer table, then you have more complicated maintenance issues, if the officerep, or rep changes

And this is termed dependence

BUT if the office rep is NOT dependent on the rep - then you DO need to store both the rep and office rep.

Ths is what you are looking for when normalising - dependencies - normally identified by repeating groups. several columns are always the same for a given set of values.

===========
so you may decide to use this lookup table technique for all the fields with multiple values, such as TOWN, EXACTCODE etc etc

============

The only area that it seems to me that you may REALLY need to normalise is because each customer has up to 3 different addresses.

Now these do belong in another table, so you have one table

customer (customerid, all the other stuff)

customeraddresses (customerid, addrerssdetails)

so the address/town/postcode etc all go out of the customer tasble, and into a new table - and now you are not even limited to just 3 addresses per customer - you can have as many as you want.

============
Just one other thing

THe search code is probably not useful at all - this is a bit of hangover from older text based systems, to help you find information. In access, you can just as easily search the name field for matches with part of a name - its far easier than having to remember the search string you set up.

It may be useful as some sort of alternate sort mechanism ,rather than for searching.