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 |
|
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 ReportingTrade ReportingBalance Sheet ReportingDeductions ReportingPFME 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 OLTP2)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 UNIONJack Vamvas--------------------http://www.sqlserver-dba.com |
 |
|
|
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? |
|
|
|
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 optimiseJack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
|
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? |
|
|
|
|
|
|
|
|
Many columns in a Table or few?