CD Baby rewrite : what are gift certificates, anyway?
by Derek Sivers
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))
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,
used boolean not null default false,
customer_id int REFERENCES customers(id) ON DELETE RESTRICT,
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?
I've always thought of it the same way you are thinking of it now, but...