March 17, 2010 at 11:09 am
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.
QA 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 table. To then merge the data into the new tables.
CUSTOMERS TABLE
ID Company Name Address 1 Address2CountyQAQ Contact Q Address1
1Red Cars Roger Doger Red House Red StEssex0NullNull
2Green CabsBilly BullhatGreen House Old StLondon1John Boy 44 Egg Rd
5Blue Carts Peter PerfectMint HouseOne StDorset0Null Null
6Blue Carts Ltd Joe Bloggs Mint HouseOne StDorset1Rio YunHull House
8Blue Carts 1 Peter PerfectME7 HouseLong StKent0Null Null
9Blue Carts 2Gorden Green SE1 Park Comb RdLondon0Null Null
March 17, 2010 at 11:20 am
The data you provided and the statement of the problem make this look like homework. Is it?
You'll need to analyze the data to work out how to break it down into whatever normal form you're aiming for. It'll likely be different if you want 3NF vs 6NF, and so on. Once you've decided what will be best for your situation, then you can begin designing the new tables.
Everything else depends on what normal form you want to achieve on this.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 17, 2010 at 12:20 pm
I'm curious too GSquared...
http://www.sqlservercentral.com/Forums/FindPost883675.aspx
The other post seems to be with the same Access DB. I wold love to see what Mr. Bullhat's house looks like on 44 Egg Rd.:w00t: Or if Joe Bloggs has his on blog.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
March 18, 2010 at 8:25 am
Hey Guys Many Thanks for your post ..... lol I wish it was as easy as a bit of Homework !!!
But this is a live system that I am planing to convert.
Of course the data is not legit its the structure of the data that I am questioning. This table has 3000 records Ideally I would like to get the database to 3rd normal form but thats not a essential requirement. I am usure of exactly how I would automate the import of data into the new tables as currently in my example I have 4 companys called Blue Carts so I would make one company called 'Blue Carts' but then 3 branch's not 4 because one is a duplicate address but a different contact name so was thinking of something like this does this look any good and any ideas on how to automate the imports ?
Address
-------
AddressId Int PK
Address1 VARCHAR
Address2 VARCHAR
City VARCHAR
Contact
--------
ContactID INT PK
ContactName VARCHAR
Company
--------
CompanyID INT PK
CompanyName VARCHAR
Branch
--------
BranchID INT PK
CompanyID INT FK
AddressId INT FK
BranchName VARCHAR
Customer
--------
CustomerID INT PK
ContactID INT FK
BranchID INT FK
QA BIT
QContact VARCHAR
QAddress1 VARCHAR
March 18, 2010 at 8:32 am
The normalized tables look okay.
The way I usually do that kind of thing is use the Output clause to tie the ID values from the new tables back to the rows in the old table.
For example:
insert into dbo.Company (CompanyName)
output inserted.ID, inserted.CompanyName into #tCompanies (CompanyID, CompanyName)
select distinct CompanyName
from dbo.OldTable;
You have to create the temp table first, preferably using the same data types as the permanent table.
Then you can join that back to the original table (I called it "OldTable" in the query, you'll need to use the real name). Doing that allows you to insert into the Branches table. Step through one normalized table at a time, grabbing the IDs as you go by using the Output clause into a temp table for each.
Works great.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply