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
 General SQL Server Forums
 New to SQL Server Administration
 Many columns in a Table or few?

Author  Topic 

PanzerAttack
Yak Posting Veteran

71 Posts

Posted - 2011-05-26 : 20:45:55
I hope this is the right place for this q.

I'm going to begin building an sql 2005 server database. What's the advice on design. I have 5 subjects:

Sales Reporting
Trade Reporting
Balance Sheet Reporting
Deductions Reporting
PFME Reporting

& 85 Attribute tables (the ones with the primary keys to look up the descriptions).

My intention is to have 1 table per subject and populate these daily with flat file SSIS imports. The Tables will only have coded items, no descriptions as these will only be held in the 85 Attribute tables.

Does this sound reasonable? It means the 5 tables have upto 50 columns of data, is this okay? My thinking is it would be easier to query if the data was all together.

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2011-05-27 : 02:09:29
Without knowing more about your application and how you will be querying, some considerations in having multiple tables or 1 table are:
1)The more foreign keys - then the more JOINS which has a performance hit, although the "fastest" design does not necessarily mean it's the best design . This is dependant on whether this is more more of a DSS or OLTP
2)The advantage of multiple tables is it's more self-documenting
3)If you decide to use multiple tables approach , consider using a VIEW with UNION

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page

PanzerAttack
Yak Posting Veteran

71 Posts

Posted - 2011-05-27 : 12:23:50
Thanks very much, just a quick add.

If I have one query creating a "Big Pivot" and therefore has many joins and is slow.

If I then have another which is smaller, creating a separate Pivot.

When a user accesses the second Pivot, will the performance be affected by the first?
Go to Top of Page

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2011-05-28 : 01:22:19
Realistically , you'll have to do some performance benchmarking. If the the Big Pivot is taking resources and the system is struggling , then, yes is the answer to your question. Look at the cache plans and optimise

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page

PanzerAttack
Yak Posting Veteran

71 Posts

Posted - 2011-06-02 : 15:38:23
Thanks for the response, I wondered if anyone on the board had any experience either way?
Go to Top of Page
   

- Advertisement -