Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Bubsalubsa
Starting Member
1 Post |
 Posted - 2010-03-23 : 15:05:45
|
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.I have tried to normalise the database structure further below this table contains about 3000 records and maybe 300 duplicates so can manually handle some of the problems.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 Null2 Green Cabs Billy Bullhat Green House Old St London 1 John Boy 44 Egg Rd5 Blue Carts Peter Perfect Mint House One St Dorset 0 Null Null6 Blue Carts Ltd Joe Bloggs Mint House One St Dorset 1 Sol Perfect Hull House8 Blue Carts 1 Peter Perfect ME7 House Long St Kent 0 Null Null9 Blue Carts 2 Gorden Green SE1 Park Comb Rd London 0 Null NullThe 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. I would also need to import the conatacts into a contact table again removing duplicates.Here is rough idea of my normalised tables that I am looking to import into.Address-------AddressId Int PKAddress1 VARCHARAddress2 VARCHARCity VARCHARContact--------ContactID INT PKContactName VARCHARAddressId IntCompany--------CompanyID INT PKCompanyName VARCHARBranch--------BranchID INT PKCompanyID INT FKBranchName VARCHARCustomer--------CustomerID INT PKContactID INT FKBranchID INT FKQA BITQContact VARCHARQAddress1 VARCHARAny help with the sql query or logic would be great. One idea I was thinking of was adding to the flat file two coloumn named companyname and branchname writing a sql query updating the companyname = company and branchname = town then manually update the duplicates (All the Blue carts would be called Blue Carts in the new field companyname) and see if it is easier to import like that. |
|
|
|
|
|
Database Design Issue