Gift Certificate System

I’m working on a gift certificate system and would like putting together a schema and need a little help as to do it.

Gift Certificate would have unique numbers
then obviously the easiest scenario would be if the person used up the whole amount but i’m thinking if someone used partial…

So there would be a debit and credit column … probaby could be done with 1 table? let me know your thoughts

id     |     debit     |     credit     |    date_modified   
-----------------------------------------------------------------
434   |      0          |     50         |      09-02-2013 11:15:36
434   |      15        |     35         |      09-02-2013 12:12:54

I’m guessing that once issued, you can’t add to a gift certificate? If so, I fail to see the point of the “credit” column.
Other than that, I would do it as a separate table like you suggest, because then you can always so the client later, when they purchased something, and what it cost.
Also, I’m hoping you’re not using auto_increment id’s for the gift codes? Those are quite easy to guess :wink:

I think the credit column is the initial value and the debit column can be anything up to that value. It can be for reference and so you know the maximum amount the debit can go up to.
The debit column would need to be altered every time some money is removed from the certificate.

Right, if you use one record for a gift certificate that would work. I was thinking of having one record for the record itself, and then separate records for each transaction using that certificate, so that the current value is initial value - sum(value of transactions so far)

Something like this, maybe?

transactionID, amount, cardID, timestamp

1, 50, 9564565323401, 2013-09-02 14:40:01
2, -15, 9564565323401, 2013-09-02 16:20:21
3, -10, 9564565323401, 2013-09-02 17:06:32
4, 5, 9564565323401, 2013-09-02 19:14:27

This way you have a record of transactions, a gift card ID that isn’t tied to an auto_increment value, and you just sum the values of all the transactions for a specific card to get the current amount.

The auto_increment wouldn’t be the gift card id lol

each gift card will have a random number + security code… 9564653561001 501

so i would most likely need a transaction table… which i didn’t think about … credit is the amount the gift card would be issued.

So any gift card could be issued any amount from $5 - $100;

So i would need a table the is the gift card info

Gift Card Table

id | card_number | card_sec | value | date_created

1, 9564653561001, 501, 50.00 , 2013-09-02 14:40:01

Transaction Table

id, giftcard_id, transaction_amount, transaction_type, date giftcard_id from gift card table (for normalization)

1, 1, 10.00, credit, 2013-09-03 15:40:01
2, 1, 14.00, credit, 2013-09-03 09:26:01
3, 1, 5.00, credit, 2013-09-04 12:54:23

I think this seems right.

If you’re going to go with transactions, why do you need a value column in your gift card table? As soon as there is a credit or a debit, it will be outdated. Storing a calculated field isn’t generally considered a best practice. (see here).

IMHO, the easiest approach would be to make the first transaction the initial value/credit.

Why do you need the “transaction_type” column? You should be able to tell if a transaction is a credit or a debit simply by the positive or negative number in the “transaction_amount”.