whats the best set up?

[bhavin_85]

hey guys

 

I need to create a new table in my sql database to store points for users.

I need to store the total number of points that the user has, but I also need a history of every time the points change.

 

A customer gains points when they make a purchase, it is automatically calculated as a % of the transaction total (this already done)...they can then redeem these points at a future point, which would obviously decrease the number of points they have.

 

So whats the best table set up for this?  ???

[per1os]

The best way is to do something like this:

 

create table user_points (
upointsid INT(11) AUTO_INCREMENT NOT NULL,
userid INT(11) NOT NULL,
pointid INT(11) NOT NULL,
createddate INT(11) NOT NULL,
PRIMARY KEY (upointsid),
INDEX(userid,pointid)
);

create table points (
pointid INT(11) AUTO_INCREMENT NOT NULL,
points INT(6) NOT NULL,
PRIMARY KEY (pointid),
INDEX (upointsid)
);

 

Using this way you can have all the history of the points what the createddate would do is allow for you a way to keep track etc.

 

That could probably use some tweaking but my brain is drained right now. Hope that helps.