Freebies
We wanted everyone to go in the database, be they a speaker, organiser, helper, sponsor... anyone. To do this, we have to be able to give people a registration code that would allow them to enter their data and not be charged (ie, not get them listed in the bad books and have naging messages sent to them every N hours!).
At the same time, we wanted to preserve some security on our database. Our goal was that no table should have UPDATE or DELETE privs for the web user ID.
We engineered this by using the Special Requirements field to be a trigger. If a certain phrase was entered, it would result in a percentage discount being applied to this registration.
We implemented this with two tables: one listing the discounts, and one containing the use of these discounts.
CREATE TABLE Discounts (ID smallint primary key auto_increment,
I wanted to be able to talk about things by ROW ID...
Name varchar(100) unique,
I used this to indicate who or what the discount was for, eg, 'IBM Delegate'.
Access_Code varchar(100),
The magic code (in square brackets) that had to be entered for this discount to trigger.
Discount_Percent tinyint,
... which was generally set at '100'!
Count tinyint);
This was often '1', but sometimes we had times where multiple discounts were allocated per code.
As each discount was used up, and entry was put into the Discounts_Used table, and before each discount was allocated, we check this and ensure we havent already used up all the available discounts...
CREATE TABLE Discounts_Used(ID int primary key auto_increment,
Again a ROW ID.
Discount_ID smallint,
0..100
CustNum varchar(255),
A reference to the Person table. We now know which Person used this discount. (We could also "select * from Person wher Special_Requirements like" but this is quicker).
Date datetime,
When was the discount used...
IP varchar(255));
... and from which IP address.