Normilisation help

[Gazza2]

Hope this is in the right forum

Ok im trying to understand normilisation and am a bit baffled.

I have a table(TblCustomerDetails) with the following fields:

Accountcode
CustomerName
Address1
Address2
County
Postcode
TelephoneNo
FaxNo
E-Mail
Website
DAddress1
DAddress2
Dcounty
DPostcode
Contact
CTelephone
CFax
CE-Mail
CreditLimit
PaymentType

Now if I understand it correctly this one table should be broken down into 4 tables:
TblCustomers
TblDeliveryAddress
TblContactInfo
TblAccountDetails

Is this correct or am I misunderstanding something(or everything)

Thanks
Gareth

 

[boblarson]

Not necessarily. You have to ask several questions:

1. Will a customer ever have more than one address? If not, it can stay together with the customer name.

2. Will a customer ever have more than one delivery address? There's a good chance of that, so that should be in a separate table as a one-to-many relationship with the customer.

3. Will a customer ever have more than one contact person? Again, a good chance of that so another table.

4. Will a customer ONLY ever have one payment type? Not likely so it should be separated from the customer table.

And there are more questions you can ask yourself about the various data elements.

 

[David Eagar]

It will depend on 1 to many relationships. For each Customer, there will be many contacts, as there might be more than 1 Delivery address.

You know your data best, just map out where there are more than 1 of anything and for those you will need seperate tables

D'oh, Bob beat me

 

[Gazza2]

Thanks for the quick replies guys

makes a bit more sense now

 

[Gazza2]

Ok ive seperated my tables into the four tables.

And ive hit a bit of a snag when it comes to adding a new customer, I created a single form based on a query(including all fields from all 4 tables) to add a new customer and the relavant details but it doesnt work(The form is completely blank , no fields and no labels, nothing).

So im now thinking that i should make a single form with three subforms or is the query not right.

All the tables are linked to the customers table in a One(TblCustomers side) to Many(other three tables) relation by Accountcode(Primary key in customers table and field in the other tables).

Hope this makes sense

Thanks
Gareth

 

[David Eagar]

Ok ive seperated my tables into the four tables.

And ive hit a bit of a snag when it comes to adding a new customer, I created a single form based on a query(including all fields from all 4 tables) to add a new customer and the relavant details but it doesnt work(The form is completely blank , no fields and no labels, nothing).

So im now thinking that i should make a single form with three subforms or is the query not right.

All the tables are linked to the customers table in a One(TblCustomers side) to Many(other three tables) relation by Accountcode(Primary key in customers table and field in the other tables).

Hope this makes sense

Thanks
Gareth

A form based on the Customer table with the three linked subforms is on the right track (or at least that is how I would do it) It would depend on how much data will be in the subforms and is it user friendly.

 

[The_Doc_Man]

Just remember to link the main form to the sub-forms through the appropriate linking fields. If you build the main form first but leave spaces for the subs, then build the subs, then go back and use the sub-form control wizard to add the sub-forms, it will ask the right questions. If you have predefined the relationships between parent and child tables, the wizard can see that and will change its question to ask whether you want to use the fields named in the relationship. It will then do "the right thing" by you if you answer Yes.