SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Addict Shaydez's Avatar
    Join Date
    Jul 2006
    Location
    Boca Raton, Florida
    Posts
    356
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    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

    Code:
    id     |     debit     |     credit     |    date_modified   
    -----------------------------------------------------------------
    434   |      0          |     50         |      09-02-2013 11:15:36
    434   |      15        |     35         |      09-02-2013 12:12:54
    Sr. Website Developer and Internet Marketing
    www.CarlosJa.com Note: If anyone
    needs to get ahold of me please feel free to email me through
    my site. Apparently i missed quite a few private messages.

  2. #2
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,080
    Mentioned
    153 Post(s)
    Tagged
    2 Thread(s)
    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
    Rémon - Hosting Advisor

    SitePoint forums will switch to Discourse soon! Make sure you're ready for it!

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  3. #3
    SitePoint Mentor silver trophy
    Rubble's Avatar
    Join Date
    Dec 2005
    Location
    Cambridge, England
    Posts
    2,410
    Mentioned
    81 Post(s)
    Tagged
    3 Thread(s)
    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.

  4. #4
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,080
    Mentioned
    153 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by Rubble View Post
    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)
    Rémon - Hosting Advisor

    SitePoint forums will switch to Discourse soon! Make sure you're ready for it!

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  5. #5
    Barefoot on the Moon! silver trophy Force Flow's Avatar
    Join Date
    Jul 2003
    Location
    Northeastern USA
    Posts
    4,606
    Mentioned
    56 Post(s)
    Tagged
    1 Thread(s)
    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.
    Visit The Blog | Follow On Twitter
    301tool 1.1.5 - URL redirector & shortener (PHP/MySQL)
    Can be hosted on and utilize your own domain

  6. #6
    SitePoint Addict Shaydez's Avatar
    Join Date
    Jul 2006
    Location
    Boca Raton, Florida
    Posts
    356
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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.
    Sr. Website Developer and Internet Marketing
    www.CarlosJa.com Note: If anyone
    needs to get ahold of me please feel free to email me through
    my site. Apparently i missed quite a few private messages.

  7. #7
    Barefoot on the Moon! silver trophy Force Flow's Avatar
    Join Date
    Jul 2003
    Location
    Northeastern USA
    Posts
    4,606
    Mentioned
    56 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by Shaydez View Post
    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
    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.

    Quote Originally Posted by Shaydez View Post
    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
    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".
    Visit The Blog | Follow On Twitter
    301tool 1.1.5 - URL redirector & shortener (PHP/MySQL)
    Can be hosted on and utilize your own domain


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •