Large Table or Many Tables?

[Atli]

Hi.

You should never have to dynamically alter your table structures.
Having multiple columns per row to store the same kind of data is also not a good idea.

You don't actually have to create 50 fields for each week of the year.
You simply create 50 fields and record when the current row was added.
Then you can simply use that data to sum up the data collected for each week.

For example, if I owned a casino and I wanted to record each time a slot machine gave out a jackpot, I might do something like:

CREATE TABLE SlotJackpots (
  EntryID Int Primary Key Auto_Increment,
  SlotID Int Not Null,
  Amount Float Not Null,
  Created TimeStamp Not Null
);

Where a new row would be added each time a machine gave a jackpot.

Then, to get a list of the jackpots and winnings given by a single machine for every week of the year, I could do:
[code=mysql]
SELECT
WEEKOFYEAR(Crea ted) AS `Week #`,
SUM(Amount) AS `Total Amount`,
COUNT(SlotID) AS `Total Jackpots`
FROM SlotJackpots
WHERE SlotID = 1
GROUP BY `Week #`;
[/code]
Which might return something like:

+--------+--------------+----------------+
| Week # | Total Amount | Total Jackpots |
+--------+--------------+----------------+
|     47 |          100 |              2 |
|     48 |          140 |              2 |
+--------+--------------+----------------+

It's obviously not a perfect design, but you get the point?

[kolanupaka]

Less columns the better and good design

Hi,
I would even suggest you make it lesser than 52 column, It would be even easier for you have data and rows added as per dates (or week no and year). The data retrieval could be based on your date range. You may end up with more data than your previous one with (52 column design), but will be easier and efficient with good design.
HTH,

Giri

[TheServant]

Hi everybody,
I posted a similar question about 6~12 months ago with no definate answer, so I will give it another go, as the choice has popped up again in my programming life:
If the number of entries is the same is it more efficient (better) to have a single table with many columns, or many tables with few columns? The obvious answer is a single table, because there is less closing and opening tables, but the reason why I ask is what about searching through a lot of columns being even more inefficient?

The situation is I need to record data for every week of the year. Say ~50 columns per week --> 2600 columns. Or, do I split it so it's 52 tables with 50 columns each? In each of these options the user (or user ID) will be the primary key.

The seemingly pointless other option is to insert new columns with every new user, and have the week-item as the primary key?

Thanks in advance.