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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2003-12-15 : 10:13:39
|
| Zaac writes "1) I am plaaning to design a table to hold the Rates of product on daily basis. on each date the rates will be different. i have about 500,000 records to store into the table. if i save each record for the whole year then i have to save a rate for each date. Means then i will be having approxTotal Rows = 500000 * 365 Total Columns = 15.2) On the other side i have an other idea if i can store the whole year in form of coulmns. Make each date a column.In this way i will be having 14 normal Coulmns365 Dates Columns (Numeric Type)365 Rates Available for particular Date Columns (Bit)Total Rows = 500000Total Columns = 744Can any one suggest me if i should go for the Second logic or not. What will be the disadvantages for using such a large number of columns in a table?" |
|
|
Amethystium
Aged Yak Warrior
701 Posts |
Posted - 2003-12-15 : 10:29:35
|
| I don't think you could even have that many columns in one table and without even thinking about it too much I don't think it's a bad idea, no it's a terrible idea!How about you sticking to a Rates table like :ProductId, EffectiveDate,RateKeep it simple man!________________Make love not war! |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-12-15 : 11:07:53
|
| Do a search for normalisation (or normalization).==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2003-12-15 : 11:36:13
|
| A better solution:CREATE TABLE MyTable (MyID IDENTITY(1,1) ,-- 14 NORMAL COLUMNS HERE)CREATE TABLE MyDates (MyID INT NOT NULL, -- Points back to a specific row in MyTableMyDate INT NOT NULL , -- 1 of the 365 days of the yearMyRate NUMERIC (6,2) NOT NULL -- Not sure what you were doing with Bit type? Is Rate a dollar value? Change to BIT if that's what is needed.) |
|
|
|
|
|
|
Increased number of columns in a table