loyalty program

[revvedmoto]

I came to this forum once or twice before and got some great feed back so here I am needing some guidance.
My new project is a loyalty program. Im not too sure where to start so im hoping to shoot some ideas out there and have someone correct me if im going the wrong direction.

I was thinking table A would be a customer table including info such as name phone and customer id.


Table B would be a table on transactions of adding or usage of loyalty points.

Does this sound like im headed in the right direction?

[JoeM]

Yep. Just make sure that the two table have a common key field (like Customer ID) so that they can be easily joined in a query.

[revvedmoto]

Thank you. So I Finally got to sit down and work on my database.
I have 2 tables
Table one (Customers-Table)
which includes Customer-ID,First-Name, Last-Name, Phone-number
Table two (Rewards-Table)
which includes ID, Customer-ID, Transaction-Date, Invoice-Number, Reward-Points.

[ItsMe]

Instead of
First-Name

I would use a naming convention more like
FirstName

or
First_Name

Code may interpret - as a minus symbol unless special care is used in syntax.

[revvedmoto]

Thanks for the advise.
My next project is putting in a dead line or expiration on the reward points. Any suggestions?

[burrina]

Add Start and End Date Fields for this that way you can calculate the Dead Line.

HTH

[revvedmoto]

sorry Burrina Im kind of new. Where should these dates fields be applied or better yet do you have an example.

[burrina]

In your Rewards Table.

[orange]

It will be useful to you and to others trying to offer advice if you were to write a description of all you are trying to do. Then design tables and relationships to support that business description. You can build the database in meaningful pieces --knowing that the pieces will fit together --based on your table and relationships design (data model).

Good luck.

[revvedmoto]

My biggest issue at this point is how to keep track of the rewards point as they expire. Lets see if I can explain: Lets say that points expire on the 5th day of not being used. At first glance I thought this would be easy because on the 6th day, day 1's point would be removed from the calculation. If you look at the chart below though on the 6th day you would actually be at -5 points if this was the case not 5 like it should be. Hopefully this makes more sense.

Day	Points	Balance
1	10	10
2	-5	5
3	-5	0
4	5	5
5	0	5
6	0	5

[orange]

This is my point exactly ---you're trying to figure out HOW

to keep track of the rewards point as they expire

.

It would be beneficial if you started with WHAT the business is.
It seems you have Customers who make Purchases. Based on some policy you award Customers "loyalty Points" based on
??the value, the frequency of purchases.

What are the business rules/facts? You need to know WHAT you're are trying to accomplish before you can determine HOW to do it.
Good luck with your project.

[revvedmoto]

Its a retail store. As far as how points are calculated it doesnt really matter because ill be manually typing in the value (the database wont be calculating this value).