By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,453 Members | 3,194 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,453 IT Pros & Developers. It's quick & easy.

Large Table or Many Tables?

TheServant
Expert 100+
P: 1,168
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.
Nov 25 '08 #1
Share this Question
Share on Google+
2 Replies


P: 3
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
Nov 25 '08 #2

Atli
Expert 5K+
P: 5,058
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:
Expand|Select|Wrap|Line Numbers
  1. CREATE TABLE SlotJackpots (
  2.   EntryID Int Primary Key Auto_Increment,
  3.   SlotID Int Not Null,
  4.   Amount Float Not Null,
  5.   Created TimeStamp Not Null
  6. );
  7.  
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:
Expand|Select|Wrap|Line Numbers
  1. SELECT
  2.     WEEKOFYEAR(Created) AS `Week #`,
  3.     SUM(Amount) AS `Total Amount`,
  4.     COUNT(SlotID) AS `Total Jackpots`
  5. FROM SlotJackpots
  6. WHERE SlotID = 1
  7. GROUP BY `Week #`;
  8.  
Which might return something like:
Expand|Select|Wrap|Line Numbers
  1. +--------+--------------+----------------+
  2. | Week # | Total Amount | Total Jackpots |
  3. +--------+--------------+----------------+
  4. |     47 |          100 |              2 |
  5. |     48 |          140 |              2 |
  6. +--------+--------------+----------------+
  7.  
It's obviously not a perfect design, but you get the point?
Nov 26 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.