My Secret Santa Project

PROJECT

I am putting together an online application that will allow a person (or persons) to run a Secret Santa (or otherwise generic Polly-Anna) gift exchange. It is going to be richly-featured, and that's where you come in.

PURPOSE

I need your insight, your suggestions, your critiques. I'm going to show the layout of the database tables that will drive the application; they will be presented in a layperson-consumable format. What I need from you is any sort of suggestion, comment, or criticism you can make. Ask questions if something isn't clear.

GUIDE

A database is made up of several tables. As of now, this application is only using one database. Table names are presented thus, and are in the large font at the top of the boxes like this one. Each table is made up of several columns. Column names are presented thus. Each table used in the application will have its format displayed (with a sample row or two). For every row in the table, each column has a value. Values will be displayed thus. Simply put, databases are like spreadsheets, and each table in a database is like a page in the spreadsheet.

BASICS

Here are the first three tables. They do not link to each other, but they are the foundations of the links made in the rest of the tables.

members

id name email password question answer
4 Jeff Pinyan japhy.734@XYZ.com ****** what did... ******
8 Kathy Pinyan kpinyan@XYZ.com ****** what is... ******
20 Chuck Pinyan ctpinyan@XYZ.com ****** how many... ******
31 Kristin Canzano jerseyleft@XYZ.com ****** who is... ******
The members table lists people who have registered to use the web site. Each person is assigned a unique id (for internal use). Their password is encrypted for safety. There are also question and its encrypted answer, which are the security question and answer: if you forget your password, answer the security question and you will be emailed a new password.

exchanges

id title start_date end_date degree price_min price_max
1001 Pinyan Family 2006 2006-06-01 2006-12-25 2 40 60
1012 Rutherford Crew 2006 2006-11-01 2006-12-18 1 20 30
The exchanges table lists the various gift exchanges being run by the application. Each exchange is given a unique id (for internal use). Each exchange has a start_date and an end_date. The start_date determines the deadline for registering for the particular exchange; on that date, the assignment of Secret Santas is done. The end_date is simply there to remind the members of the exchange when the gift-giving is happening. The degree column denotes how many givers are assigned to each receiver (and vice versa). The two columns price_min and price_max give the spending guidelines for the exchange.

guests

id name email confirm_code
53 Jennifer Pinyan jpinyan@XYZ.com Rz02NN1
The guests table lists the people currently being invited to the web site (i.e., they are not yet members). Each person is assigned a unique id (for internal use). They also get a random confirm_code which is sent to them via email. They must use that code when registering; this is a standard registration procedure.
Now that we've seen the three foundations of our infrastructure, let's take a look at the types of relationships we need to build between them.

LINKS

For every exchange, we need to know who the participants and administrators are; this links the tables exchanges and members. We'll call this table participants.

participants

exchange_id member_id role
1001 4 member
1001 8 admin
1001 20 admin
1012 4 both
1012 31 member
This table links a member (via member_id) to a gift exchange (via exchange_id) and denotes their role in the exchange; role can be one of member, admin, or both. An administrator of an exchange is someone who can see who is assigned to give a gift to whom; usually this person is not a participant, but the option exists to allow this.
We want it to be possible for the administrator of a gift exchange to invite people into the exchange; however, those people might not be registered with the web site yet. We'll make two tables, one for each possibility: invite_member (linking exchanges and members) and invite_guest (linking exchanges and guests).

invite_member

exchange_id member_id
1001 31
This table invites a member to an exchange on behalf of the exchange's administrator(s).

invite_guest

exchange_id guest_id
1001 53
This table invites a guest to an exchange on behalf of the exchange's administrator(s).
Now we need a table that lists who is giving to whom in which exchange. This table effectively links three tables together: two instances of members and one instance of exchanges.

assignments

exchange_id giver_id receiver_id
1001 4 x
1001 4 y
1012 4 z
This table defines a particular recipient for a particular giver in a particular exchange.
We also want the ability to set up restrictions about who can't give to whom. For instance, if you don't want a husband to be his wife's Secret Santa (and vice versa), you need to state that somewhere. This is represented in the exceptions table, which is modelled very much like the assignments table. These can only be defined by administrators, so that the participants can't be picky and choosy about who they get.

exceptions

exchange_id giver_id receiver_id
1001 4 31
1001 31 4
This table defines a giver-receiver relationship that is not to be allowed for a particular exchange. This exception is strictly one-way: if we did not have the second row, that would mean Jeff could not be Kristin's Secret Santa, but Kristin could be Jeff's. The second row stops that from being possible.
Another helpful feature would be to keep track of electronic wish lists, whether hosted on another website (such as Amazon.com), stored in a Word document, or just entered into the web site as text. The wishlist table does just that.

wishlist

id member_id type data
1 4 URL http://...
2 4 file /path/to/list.txt
This table defines a wishlist for a member (and as is shown, a member can have more than one wishlist).
We then want to link wishlists to exchanges.

wishlist_link

wishlist_id exchange_id
1 0
2 1012
The special exchange_id value of 0 denotes a wishlist that is active for all of the gift exchanges the owner of that list is involved in.
More tables might come into existence later.

BENEFITS

Now that we have our tables in place, the application can be built around it. This application will have the following benefits and functions:

Non-duplication of gifts

If, as in the Pinyan 2006 gift exchange, multiple people (Jeff and Jenn, for example) are assigned to be someone's Secret Santa (Christopher, for example), then the web site will notify Jeff and Jenn of this, and give them a simple means of leaving notes or messages for one another (either through the web site or through email) about what they plan on purchasing, etc.

Automatic Reminders

Since the web site will know when a gift exchange officially ends, it will be able to send the members of an exchange reminders on occasion (such as a month before and a week before the exchange ends). A user's Secret Santa will be informed when that user has uploaded their wish list.