Table Design Issue

[Bubsa]

I have inherited a database from Access (with a VB front End) that has some critical design floors it looks much like this. Its aim is to send out quotes and then arrange collections I have re-solved soime issues with a few tables but this one I am struggling with. I have used SQL 2008 Access tool to convert the database to SQL to start re-writing to improve the performance.
As you can see if the same company has a different branch a new entry is made in the Customer Table if someone new joins the same company at the same branch again a new entry is made.

QAddy column means that if active the collection is to be made at the Q Address 1 and not Address 1.
I know I obviously need to normalise this database but this table contains over 3000 records that I need to merge into the different Tables any ideas how best to redesign this


CUSTOMERS TABLE



ID Company Name Address 1 Address2 County QA Q Contact Q Address1
1 Red Cars Roger Doger Red House Red St Essex 0 Null Null
2 Green Cabs Billy Bullhat Green House Old St London 1 John Boy 44 Egg Rd
5 Blue Carts Peter Perfect Mint House One St Dorset 0 Null Null
6 Blue Carts Ltd Joe Bloggs Mint House One St Dorset 1 Sol Perfect Hull House
8 Blue Carts 1 Peter Perfect ME7 House Long St Kent 0 Null Null
9 Blue Carts 2 Gorden Green SE1 Park Comb Rd London 0 Null Null

 

[RiverGuy]

It's hard to give advice without knowing more about your business or requirements, but this may give you some ideas:

Contact
--------
ContactID INT PK
ContactName VARCHAR
ContactAddress1
ContactAddress2
ContactCounty


Company
--------
CompanyID INT PK
CompanyName VARCHAR


Branch
--------
BranchID INT PK
CompanyID INT FK
BranchName VARCHAR


Customer
--------
CustomerID INT PK
ContactID INT FK
BranchID INT FK
QA BIT
QContact VARCHAR
QAddress1 VARCHAR

With the above logic, you store each contact only once. You define branches as belonging to companies, and your Customers table brings everything together to create Contact instances to a Branch to form Customers. I kept the QA column in the Customers table as I was not sure if these could be different for the same customer for different branches.

 

[gmmastros]

I was thinking similarly to RiverGuy, but I would take it one step further. I would have an address table, and then store the addressid in the other tables wherever you need it.

Address
-------
AddressId Int PK
Address1 VARCHAR
Address2 VARCHAR
City VARCHAR

Contact
--------
ContactID INT PK
ContactName VARCHAR
AddressId Int

Company
--------
CompanyID INT PK
CompanyName VARCHAR


Branch
--------
BranchID INT PK
CompanyID INT FK
BranchName VARCHAR


Customer
--------
CustomerID INT PK
ContactID INT FK
BranchID INT FK
QA BIT
QContact VARCHAR
QAddress1 VARCHAR

This way, you can easily toss an addressid in to your other tables. One for the company, one for the branch, one for the contact, one for the customer, etc...

If you need to add a bit column for the QA part to the address table, no problem.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Bubsa]

Guys many thanks for your posts has been helpful. My paper design was similar to River Guys but I like the idea of the extra Address table though I would put this into the branch table and not the contact.

Reason being the data is stored only so that when you press a button on the application it retrieves the data and makes out a invoice header and collection note to (Person Name and Address) without manually typing in. That is the sole reason for this table.

The biggest problem I have though is how would you automate the import so the data, merge's correctly into the new database tables. In my above example we have 4 Blue Cars in the company column I would need to merge this into one company but only 3 branches as the 4th Blue Car is there only because of a different conact name but it has a duplicate address.