Partition Tables or Settle for Blank Fields?
OK, the guys at my project don't have a ton of experience with database design and performance planning, so we're hoping someone can give us some insight about the cost/benefits that these two scenarios might have.
Scenario #1
-------------
We have a single table that keeps the statistics of american football players. This table, called "realplayer_stat" has both offensive stats and defensive team stats included within it with each player having a record for 22 weeks in the season.
Twenty columns are offensive stats and ten are defensive team stats. There are 7,380 offensive players and 120 defensive teams. No offensive players will earn defensive stats and vice versa.
realplayers records: 7,500
realplayer_stats records: 165,000 (7,500 X 22 weeks)
realplayer_stats fields: 4,950,000 (165,000 recs X 30 columns)
**Guaranteed zero/null fields: 1,623,600 (7,380 recs X 10 fields X 22 weeks)
**Number of defensive fields used: 26,400 (120 X 10 fields X 22 weeks)
Scenario #2
--------------
We can partition the realplayer_stat table vertically so that the offensive stats (20 columns) and defensive stats (10 columns) are now separated.
The new realplayer_stat_offense table will have 7,500 records, but 10 less null/zero fields per record.
The new realplayer_stat_defense table will have 120 records, and only consist of 10 fields per record.
realplayers_off records: 7,380
realplayers_def records: 120
realplayer_stats_off records: 162,360 (7,380 X 22 weeks)
realplayer_stats_def records: 2,640 (120 X 22 weeks)
realplayer_stats fields: 3,247,200 (162,360 recs X 20 columns)
realplayer_stats fields: 26,400 (2,640 recs X 10 columns)
Question
---------------
We have one developer who doesn't feel the 10 extra nulls in the first scenario really matter performance-wise. He likes to keep as much data in one table as possible and doesn't think that the small number of records overall (165,000) creates too much of a problem for MySQL to manage. He's afraid that partitioning tables will create another level of maintenance/overhead that a single table will prevent.
We have another developer who wants to partition the data, since he feels that the 10 null/zero fields per record will create a rather large performance drain on the system. He also doesn't think the partitioning will be that big of a deal in database management.
Can anyone offer any insight?
wrburgess