Mysql – Database structure for affiliate system

database-designMySQL

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:

AffiliateUser

+----------+--------+-------+-----------+-------------+-------+
|   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?

Best Answer

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.

Related Question