registration table

broker_id , email ,        affiliate_id.....etc
40          abc@gmail.com  9999 
62          xyz@gmail.com  8888     

affiliate_Stats table

status_id , affiliate_id , banner_id , campaign_id , clicks   impressioins ,    ctr , sale , sale_price, total_earned...etc
27          9999            3              0               0      1              0  
29          9999            3              0               1      0              0


id name recruitedby user_id

17  Test   0         40
18  check  17        62

Above is my tables structure in mysql. i want to make a three tier affiliate system...
i am just tracking if some one viewed banner and then just saving records as you can see impressions is "1". if user clicks on
banner , then another records will save and i am storing clicks "1". i have some doubt can any one guide me to achive in write way

  1. i am just struggling with database design for above (not sure).
  2. How to set above table in correct way.
  3. i am keeping sale , price ..etc in stats table is it write?

any help will be apprciated.

3 Years
Discussion Span
Last Post by mangel.murti

If you keep to the first 3 normalization forms, you should find the solution. It would help if you listed ALL the fields of each table and said which fields are RELATED (PK-FK).


Thanks diafol for your reply.i just go through again the database structure and just modify above. i am building a three tier affiliate system.so trying to achive above. i am just attaching tables structure images. there is chain table where i am storing data as who recuited whome. so i want to achive below

**Refer someone A = you will receive 15% of earnings generated by A, every month , A refers B = you will receive 2% of earnings generated by B, every month B refers C = you will receive 0.5% of earnings generated by C.**

what changes needs to be done in chain table?

any one here?

Attachments click.JPG 71.62 KB sale.JPG 126.66 KB chain.JPG 56.53 KB
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.