Two-Tier Database Schema

[dColumbus]

I'm designing a database schema that will track two levels of commissions... the first level earns a commission on their sales, then, they earn a smaller commission on the sales of those that they brought in...

User1 sells to User2 ... User1 makes a commission.
User2 selles to User3 ... User1 makes a small commission, User2 makes a commission. Two levels deep.

Can I pick your brains for a logical and clean solution?

[jwilliam]

Maybe something like this:

create table users (
id int auto_increment not null,
name varchar(),
...
level int not null,
parent int not null,
primary key(id)
) Engine=InnoDB charset=UTF8;

create table commissions (
id int auto_increment not null,
amount float not null,
date int not null,
user int not null,
foreign key(user) references users(id) on update cascade on delete cascade,
primary key(id)
) Engine=InnoDB charset=UTF8;

You can handle most of the commissions logic in code... so if a user makes a sale you check his level and calculate the commission. Then check to see if he has a parent and, if so, calculate his commission as well. If you wanted, say, all the level 2 commissions for a report, you could select all the level 2 users and join on commissions to pull the data.

I wouldn't make it any more complicated than this unless there was a good reason to.