| Author |
Topic |
|
mattgosling72
Starting Member
8 Posts |
 Posted - 2012-01-25 : 10:21:44
|
| Hi,I need to put together a stored procedure to update injection rates for certain parts in the db.This will be run every fortnight and to allow the trends to be followed I need to keep 12 months worth of changes (so 26 updates in a year).There are 5 pieces of data that we need to track for each part over that period to allow us to see trends.This would result in 130 columns worth of data for each part.Is this too many columns for a single table? I know it isnt near the maximum number of columns allowed, but is this bad practice? regardsMatt |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-01-25 : 10:45:21
|
| i dont think you need 130 columns. is the data your tracking is same why do you need seperate fields for each periods? why not put them as rows and then add period also as a field to indicate period that data belongs to?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
mattgosling72
Starting Member
8 Posts |
Posted - 2012-01-25 : 11:06:23
|
| I dont (specifically) need separate fields for each period and what you suggest should work. .for the first test run of the sp I have > 10,000 rows. is it be better to have > 260,000 rows with 7 columns than 10,000 rows with 130 columns? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-01-25 : 11:44:16
|
| yep...it might be better to do it latter way.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2012-01-25 : 11:52:22
|
quote:
Originally posted by mattgosling72
I dont (specifically) need separate fields for each period and what you suggest should work. .for the first test run of the sp I have > 10,000 rows. is it be better to have > 260,000 rows with 7 columns than 10,000 rows with 130 columns?
Yes, for many reasons.For example, try writing a query to give you the average, min, and max of each part over the 11 months using each design. You will quickly see how much more complicated the 130 column design makes it.Also, what do you do if they decide they need the data from 3 years, instead of one? Add another 260 columns?What if they decide they need the data weekly instead of monthly?CODO ERGO SUM |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-01-25 : 11:55:04
|
| and performancewise you can even add an index on period field if you're using latter approach which will really help in speeding up query and in case of large datasets you can even go for partitioning based on period column------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
mattgosling72
Starting Member
8 Posts |
Posted - 2012-01-26 : 02:52:31
|
| Thanks for the responses.All makes perfect sense when explained! |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-01-26 : 08:46:35
|
| welcomelet us know if you need any more help------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
Number of Columns in a Table