Help with customer account problem

[wxman2]

Hi. I’m new here!

I know that there is a simple answer to my problem, but I think I’ve been working on it for too long now so I can’t see it!

I’m making a database driven website for ecommerce that has only one product, lets just call them “units” for now. What I need to do is have tables for customers, admin people, and then a way to track how many units each customer has in an account. That’s really no problem. My problem come up when I try to figure how to track who is editing the account data.

For example:
Customer1 buys 10 units. The next day he wins 10 more units in an online contest, so Admin1 needs to add 10 more units to Customer1’s account. Some time later, the boss needs to look up Customer1’s transactions to see what they did over a period of time. In the lookup, the boss needs to see who added or subtracted from Customer1’s account. I just can’t seem to find a way of splitting up the tables to make this work. In addition to this, I also need to make it, so later I can add more products, so I have a product table too.
Believe it or not, I actually have been making Access databases for quite some time now, that’s why this is really bugging me.

Thanks, Tom

 

[jrbarnett]

Hello

Why not have a transactions table:

Product, Account Code, Balance, Timestamp, Username, Comment

This way you can keep track of the product and account details and have a summary of what happened, when, and any notes relating to it (eg if it was won or purchased);
you can get the current balance by doing a sum of the balance field grouped by product and account code and add it to the opening balance.
To indicate sales, use negative values.

John

 

[wxman2]

I tried that. Right now I have Users and Transaction related to each other like you suggested. But what is one of the Admin people have to change the account balance. There’s no way that I can see, to make a relationship between Users – Transaction – Admin. It needs to be so Admin people can pull up a listing of transactions by user account, and see who added or subtracted from the account. I’m sitting in front of the computer right now trying some new ideas.

 

[jrbarnett]

Can an admin person not be a type of user, perhaps with a special flag set in the table?

John

 

[wxman2]

Now that's possible. I could just split off the tables to cover the different data for each type but have just one 'name' table.

 

[jrbarnett]

Well try that and get back here if you have any more problems or questions.

John

 

[wxman2]

Thanks for the help.