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

affiliate_chain

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.

Member Avatar
diafol

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?