> This message was sent from Geocrawler.com by "James" <captain@stripped>
> Be sure to reply to that address.
> I am designing an affiliate traking system to use on a site I am creating. I plan on
> having about 1500 affiliates at its peek. Would it be ergonomical to have 1500 tables in
> a single database? or would I need to find a way to use just a few tables to store all
> the information.
If you do this, you may want to put them into separate databases. Each
database is stored in a directory, and if you have to many files in it,
the amount of time it takes to open a table is increased. It would
probably be a good idea to increase table cache size in mysqld.
> I am wanting to track unique clicks, raw clicks and affiliate sales, to the site.
> I came up with a simple design to track the clicks and sales but the way I had it
> doesn't store the time and date of the click or sales. The time and date is very
> important for reporting sales and click stats to affiliates.
> I was using 4 different tables for everything.
> One table to hold the personal information of all the affiliates (refinfo).
> One table to track sales. The sales table has
> a field for the refid(auto increment, assigned when affiliate signs up), a field for
> the username, field for the password, and a field for each of the sale items(which in the
> near future could range near 100)
> The sale items fields would hold just numeric values that my php3 script would update
> with each hit. I have it incrementing the numeric number by one for each sale that is
> The other 2 tables would be designed with 4 fields. Refid, username, password, and
> clicknumber. The clicknumber field would be incremnted by the php3 script each time the
> affiliate sent a hit to the site.
> Can someone think of a better design for the database than this if it isnt
> ergonomical to use a seperate table for each affiliate? I am wanting to add the time and
> date of each click and sale to the database.
Your design look OK to me so far, except I would not have a table for
each affiliate. One of the reasons is that you will have a dynamic
schema with this approach - each time you have a new affiliate you have
to create a new table - this in my opinion complates maintenance, and
makes things less secure - the web user of MySQL has to have CREATE
I would still have several tables, but store affiliate id in each and
have some hashing algorithm to group the affiliates. That way it would
be easy for you to go to several servers when you have to do it.
> Thanks In Advance
> James Hicks
> Geocrawler.com - The Knowledge Archive