CD Baby rewrite : what are gift certificates, anyway?

by Derek Sivers

Sometimes in my blog, here, I won't have time to write a full entertaining narrational "article" about something, so I'll just quickly paste in some thoughts that you may find useful if dealing with similiar problems or situations on your end.

Here's one: GIFT CERTIFICATES
I used to think of them as just items in a cart, with a negative balance. But here's another way to think of them...

# The membership-account idea : SUBTRACTION ON YOUR ACCOUNT

Little Jimmy gets a gift certificate from grandma.
He comes to CD Baby, and puts some CDs in his cart.
Upon checkout, we ask him to create an account here so we know who he is.
Anywhere in the process, once we know who he is, he can tell us if he has any gift certificates.
By entering their passcode, it adds the full amount of the gift cert to his account - permanently.
He can do this with multiple gift certs, and it will keep adding to this single amount.
Whenever he's buying anything, the total cost of his order (including shipping) has this gift-cert balance subtracted from it.

== HOW IT WORKS, INSIDE:
Someone purchases a gift-cert. It does nothing but create a giftcert, (asking them for optional extra info, like who to say it's from, to, and a message with it).

When Little Jimmy comes to use it, we update that giftcert with his customer_id, used=true, date_used=now AND:
A new entry in giftcert_entries with the giftcert_id, and the negative-amount of the giftcert total.
The above two steps are a single transaction, like double-entry accounting. We took it out of one column, into another.

(A sum of giftcert_entries tells us his total gift balance: -20)

He completes his order, total $17 - so his $20 giftcert is used to pay for the order:
A new entry in giftcert_entries with the invoice_id and the positive amount of the giftcert used.

(A sum of giftcert_entries tells us his new total gift balance: (-20 + 17 = -3))



== DATABASE:

CREATE TABLE giftcerts (
id serial PRIMARY KEY,
code char(10) not null UNIQUE,
amount numeric(8,2) not null CHECK (amount > 0),
email text not null,
from text,
to text,
message text,
used boolean not null default false,
customer_id int REFERENCES customers(id) ON DELETE RESTRICT,
date_used date
);


CREATE TABLE giftcert_entries (
id serial PRIMARY KEY,
customer_id int not null REFERENCES customers(id) ON DELETE RESTRICT,
entry_date date not null default CURRENT_DATE,
amount numeric(8,2) not null CHECK (amount <> 0),
giftcert_id int REFERENCES giftcerts(id) ON DELETE RESTRICT,
invoice_id int REFERENCES invoices(id) ON DELETE RESTRICT
);


You got a better way, punk?


1 Comments

RichardLynch
2005-10-20 10:23:54
Another View
I've always thought of it the same way you are thinking of it now, but...


It occurs to me that REALLY what you have is just another form of payment.


In a sense, you are fulfilling the role of just another PayPal, credit card, COD service.


So maybe it would be more fruitful, and have less code and more OO sharing, to completely separate your Gift Certificates as if they were "like another PayPal" and treat them as such.


Sure, it will be easier to integrate because you know exactly how the Gift Certficate works, and there's a lot less risk, since you control all the numbers/codes/cash.


But treating Gift Certificates as if it were almost a totally separate company might make your life much simpler in the long run.


All the issues you would have to examine are slotted into the same category/code/review/process as you slot PayPal and CC#s and so on.


Because that's what a Gift Certificate really IS. It's essentially a deposit-required no-overdraft debit-card managed by you.


You got GiftBaby registered anyway, right? :-)