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 |
|
spireite
Starting Member
12 Posts |
 Posted - 2006-07-09 : 11:17:31
|
| Hi all,I've worked with an existing system for so long, I can't see wood for trees - and I'm torn on a structural change.Existing system.I have a table that has 700,000 rows. There are 11 attributes that could contain a company code. Each one of these attributes is named to indicate the role the company plays.. e.g. Owner, Seller, Maintainer etc...As such the structure currently is VehicleID int,OwnerCode,SellerCode,MaintainerCode,FinancingCode .... etcNow my heart says to break this out into a link table....VehicleID intCompanyCode,CompanyType (where type is an indicator for Maintainer, Seller etc). An advantage with this that where a maintainer doesn't exist, there is no entry, whereas in the original schema - its a null value.I have a nagging doubt over whether its the right thing to do - having tried it, the overheads on the queries is substantial and try what i can i cannot get performance even close.On a typical query, the cost is 3 times with the link table approach (link table has 6million rows) and I've indexed as much as I can.Can I canvas opinions? |
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2006-07-09 : 18:41:22
|
| Here is one opinion:Break out he attributes that can be null into their own tables.e.g.VehicleSellerVehicleID | SellerCode |
 |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2006-07-09 : 19:01:25
|
| As you probably know, the 2nd version is the preferred, normalized method which will provide for the most flexibility going forward. But I do understand that in some scenerios it can make your SQL more complicated and/or less efficient, depending on what you need to do.Could you provide for us a "typical query" and the DDL of the two potential table structures? We may be able to help you optimize your SQL and/or your indexing. I'm curious to see which methods you are you using to query the normalized versions.ALso, remember to not stress about the fact that in the old way, the data is lined up all in 1 row, while in the second way, it is returned one row at a time. YOu can often present the normalized data so that it all is on 1 row *without* storing it and/or returning it that way using T-SQL, which can be quite efficient.- Jeff |
|
|
|
|
|
|
|
|
Opinions sought on a tables structure