Database Design

[npertuset]

Ok. I'm trying to figure out the best/most optimized way to design my tables. I have multiple purchases go through my website. For each purchase, a percentage is given to different places (the user, company profit, and a bonus pool - more needs to be able to be added at any time). I want to keep track of all of this, be able to trace every transaction, pull up transactions at will and know the balance of each account (company profit and bonus pool will only have 1 account, but there could be 50-10,000 users).

 

One way I've thought of is to create an "account type" for each user. IE:

 

Table: Type_Of_Accounts (this table holds the option for different payable accounts)

 

id (primary key - auto increment)

name

 

Examples:

 

1 / user

2 / company_profit

3 / bonus_pool

 

Table: Transaction_Log (this table holds all transaction information.)

 

id (primary key - auto increment)

type_of_account_id (matches Type_Of_Accounts)

account_id (if it's a user account, this field will hold the user id. if not, it will be set to 0).

amount

time

 

Examples:

 

1 / 1 (user account) / 40 (user_id) / 59.99 / <time>

2 / 3 (bonus pool) / / 19.99 / <time>

 

Table: balances (this would just update each time a transaction went through, this way I don't have to calculate it on the fly each time).

 

id

type_of_account_id

account_id

balance

 

Examples: (using data from above)

 

1 / 1 / 40 / 59.99

2 / 2 / / 19.99

 

Here are some example queries:

 

Get a users transaction log: select * from Transaction_Log where type_of_account_id='1' AND account_id='x'

 

Get the transaction log for the bonus pool: select * from Transaction_Log where type_of_account_id='3' and account_id='0'

 

To get the balance of the companies profit you could do: select balance from balances WHERE type_of_account_id='2' AND account_id='0'

 

-----------------

 

What are your thoughts on this, and do you have a better way of designing this? I want it to be optimized/designed the best way possible.

[The Little Guy]

I would create a separate table for each. It will be easier to manage.

[npertuset]

like this?

 

[attachment deleted by admin]