Best design - SQL Server Forums

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Best design

Author

Topic

chrispy
Posting Yak Master

107 Posts

Posted - 2007-12-17 : 18:25:58

I am working on redesigning a table to normalize and increase performance.

Imagine the current table as such:


CustID      State    CarType         CarManufacture     CarLocation
------      -----    --------        --------------     ------------
abc          1,2,3    100,50,40       101,201,203         505,605,705

Currently the application (Cold Fusion) would lookup a CustID and then use the comma separated list from the columns above to lookup the data in a list that was stored in memory in Cold Fusion (read very poor performance) and not very flexible.

I have proposed a table design that would be such


CustStateTable

CustID         StateCode
-------        ---------
abc              1
abc              2
abc              3 

CustCarType

CustID         CarType
------         --------
abc             100
abc              50
abc              40

etc....etc..... for the other colums

and then the translation tables


StateTranslation
StateID         State
-------         -----
1                New Yotk
2                Vermont
3                Texas



CarTypeTranslation
TypeID         Type
------         ------
100             Ford
50              Chevy
40              Dodge


etc....etc..... for the other lookups

It does seem to work but I am trying to keep this as simple as possible for obvious reasons.

One major issue that we are having is what was a once a simple query has turned into a huge query with a huge amount of joins. Creates a lot of overhead to create a single query and is really a mess. It also seems overly complicated for something this simple.

Another hurdle I am having is getting the output onto one row, such as :


CustID   State1   State2   State3  CarType1 CarType2 CarType3    etc..
------   ------   ------   ------ --------  -------  --------
abc     New York  Vermont    Texas  Ford     Chevy    Dodge

So what are the thoughts on the simplest way to do this? Main goals are to keep the queries as simple as possible and to get the output onto one row as simple as possible.

Thanks in advance,
Chris

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2007-12-17 : 19:53:04

Seems good so far from what I can tell (not sure if there must be the same number of states as cars otherwise you might want to combine the tables). To get all on one row though you would need to put a limit on the number of states/cars. You'd also want a sequence number in your relation table so it all comes out in the right order.
I wouldn't worry about the joins. That what databases are for. Model your data correctly and the rest will follow. You might be able to create a view if it helps you write simpler queries.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources