I am creating affiliate tracking system and looking for best database structure to use on MySQL to place the lowest possible load on the server. There will be 1000 affiliates, and each will have statistics per day. I am thinking of this scenario:

Affiliates (main table)

| id | affiliate_id | username | password | more columns |
|  1 |      0000001 | johndeer | password |              |

I plan to create a new table for each affiliate with associated statistics:


|   Date   | Clicks | Sales | SalePrice | TotalEarned | Bonus |
| 12/12/12 |     45 |     2 |        20 |          40 |     0 |
| 12/13/12 |     12 |     3 |        20 |          60 |     0 |

Is this the correct design?

  • No, no, no. Add affiliate_id to the 2nd table and make the combination (affiliate_id, date) either the primary (or a unique) key, since you have stats per day per affiliate. Dec 12 '12 at 6:22
  • There is no reason to have thousands of tables with identical structure. Dec 12 '12 at 6:41
  • but it would create like, 1000 affiliates *30 days = 30k rows per month , wont it cause load on server for showing stats in affiliate dashboard ? via php, thanks
    – user16108
    Dec 12 '12 at 8:23
  • 30K rows per month is peanuts.
    – Joel Brown
    Dec 12 '12 at 12:49

Separating the affiliates into their own tables would usually be a bad idea. It will not save you anything and will make reporting across populations should you need to later.

I would go further than ypercube suggests: why not record each sale individually?

Clicks:    Affiliate            ClickTime
           (FK to aff. table)   (timestamp)

Salses:    Affiliate            SaleTime      SaleAmount
           (FK to aff. table)   (timestamp)   (fixed precision decimal)

Yes this will take more space, but far from a lot - if this much space is a problem then you are working with a extrememely limited hosting solution. With daily recording as suggested by ypercube each row is going to be 28 bytes (assuming 4 for date, everything else in your table being 4-byte ints, and the affiliate ID being an int also) plus index load. Assuming you need enough indexes to more-or-less double the storage requirements (for tables that do not have many columns this is not unusual, though almost certainly overkill for these tables), 30,000 rows per month is still only ~1.6Mb/month.

The above structure gives you much more detail should you need it for reports later. If you specifically need to report on the basis of days, you can either split the date and the time into separate fields or denorlamise a tad and keep the full timestamp plus a date-only field. You can then generate a daily report for each affiliate just by grouping by the date, affiliate, and data fields and using the count and sum aggregates appropriately.

With proper FKs and other indexes the extra detail should not impose performance issues. Databases are designed for handling large amounts of data (and this is far from large) in tables - millions of rows should not be a problem in that respect, never mind tens of thousands.

  • using pk and fks are new for me as i am new to mysql these, anyways i get the point , any idea what Storage Engine is good to be used for this purpose ? thanks
    – user16108
    Dec 15 '12 at 12:27
  • Primary keys and foreign keys are fundamental to RDBMSs (not just MySQL) so it is definitely worth taking some time to familiarise yourself with the concepts. From the point of view of my comment regarding performance the storage engine will use PKs and FKs as indexes (behind the scenes they are implemented as such). I've not been a mysql user for some time so I'm no expert, but IIRC both myISAM (the old default) and InnoDB (default since versions released in early 2011) support all of the above just fine though I'd generally recommend InnoDB. Dec 18 '12 at 9:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy