Page 1 of 1

Help on concept & design for membership database

Posted: Tue Feb 24, 2009 12:52 am
by phpwalker
Hi guys,

I've to design a database structure for membership point system.

It is like other normal membership point system where members can accumulate points when they purchase products.

However, some of the members points will be expired after certain periods.

Let say 3 months. For an example:
A person has gained 300 points on Jan 2008. He gained another 500 points on Feb 2008. Now, when it reaches 1st of May, he never use the 800 points at all.
The system will deduct 300 points from his account as expiry date dued. So, he left 500 points in May, and if he doesn't use it before May 31st, it will all gone on 1st of June.

Here is my idea, I store the points of the member in each month in a table. Below just simple ERD sample I think of...

member_point
(
member id(pk),
current_month,
pre_1month,
pre_2month,
pre_3month
)

So, the system will need to update the database every month to move current_month points to pre_1month column and so to others.

I know there is better way to do that, can someone give me hints and some guidance?
Correct me if I am wrong for the design, too.

Sorry for my bad english.