| Author |
Topic |
|
Surfer513
Starting Member
29 Posts |
 Posted - 2010-12-22 : 17:14:49
|
| I have a bit of a decision to make. Is it better to add columns to an existing related table, or to create another table instead?For instance, I have TableA. It contains user data by the month. Is it smart to just keep appending columns onto the end of that table as time progresses (and months progress)? Or should there be a certain table division there, and just multiple tables created?In other words, is there any disadvantage to just keep throwing columns onto a heavily accessed table?I hope I have explained my question well. Thank you in advance. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Surfer513
Starting Member
29 Posts |
Posted - 2010-12-22 : 20:38:26
|
| Ok, sorry for the vague nature of the question.The first field is a string (nvarchar[30]), and there's 36 fields and each of them are decimal(10,4).So what do you think, scale it horizontally in the same table or vertically in a new table?Thanks in advance. :) |
 |
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2010-12-22 : 21:09:58
|
| Not really enough information - but, if I am reading this correctly you keep adding new columns every month to contain that months values? Is that correct?If so, then no - that is not the way you should be building the table. You should have a separate table with a row for each month and the value for that month.Jeff |
|
|
|
Surfer513
Starting Member
29 Posts |
Posted - 2010-12-22 : 21:21:11
|
| No, sorry again for the vagueness.This table is for the year 2010. It has 37 fields (1 string, 36 decimal). The decimal fields are for each month. But come 2011, I need a solution to either add another year (36 decimal fields) to the existing table, or to build a new table vertically for the new year. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2010-12-22 : 22:21:32
|
| You should not be designing your tables this way. You should not have a table for each year, and you should not have a column for each month. So your design is flawed. But if you must stick with this design, then please do create another table. Do not add 36 columns to an existing table just to support a new set of months. Your clustered index would be seriously bloated otherwise.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
Surfer513
Starting Member
29 Posts |
Posted - 2010-12-22 : 22:53:47
|
| Tara, thank you for the constructive criticism. It is much appreciated. I understand that this design is flawed, and I have every interest in correcting it before the database and the code becomes any larger. What do you suggest?Each user could potentially have 3 purchases per month, which is why I went with the table structure:create table test_table(strUserName nvarchar(30),JanuaryPurchase1 decimal(10,4),JanuaryPurchase2 decimal(10,4),...)Can you give me a crash course as to why this is inefficient and flawed? What do you suggest as a solution to this problem? Thank you very much, Tara. It is much appreciate. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-12-23 : 05:27:14
|
| You need a separate table for Purchases containing UserName and Purchase (like you have) and also PurchaseDateThis design is then "unlimited" as to the number of purchases per month, and the date period supported. |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2010-12-23 : 07:02:41
|
"Each user could potentially have 3 purchases per month"Talk about limiting the abiliy of your applciation to make you a fortune. "You can only buy products from us 3 times a month". I'm hoping this is not a pub!  |
|
|
|
Surfer513
Starting Member
29 Posts |
Posted - 2010-12-23 : 08:17:57
|
quote:
Originally posted by Kristen
You need a separate table for Purchases containing UserName and Purchase (like you have) and also PurchaseDateThis design is then "unlimited" as to the number of purchases per month, and the date period supported.
Kristen, I see what you mean and I do agree that this is a better design. Thank you for your suggestions! |
|
|
|
|
Best practice - add columns or create new table?