On Thu, Feb 17, 2000 at 01:47:33AM -0500, David L Rubin wrote:
>I'm trying to design a db schema to keep track of information
>pertaining to my upcoming wedding. I want to keep track of guest
>contact information, who gave what gift, whether the guest has been
>sent a thank-you card, and what events the guest is going to attend,
>and whether the guest is with the bride or the groom. My question is
>whether to put all this info in one table or not.
First of all, congratulations! Please keep honesty as your first goal;
nothing is as terrible, once shared, as it is when kept inside and left
to rot your soul. If you are honest, you are also free and your life
is made simple. However, being honest isn't always simple!
OK, now on to the database. There are many ways to do it. I would
probably go with these tables:
CREATE TABLE guests (
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
last VARCHAR(40) NOT NULL, # separated for sorting
first VARCHAR(40) NOT NULL,
address VARCHAR(255) NOT NULL, # use \n for multiple lines
thanked ENUM('No', 'Yes') NOT NULL,
guest_of ENUM('Bride', 'Groom'),
PRIMARY KEY (id),
KEY name_k (last, first) # assuming you might want this
)
# You might want to split this table into a real events table, with
# event_id, event_name, event_location, event_time, etc., and the
# attends table which would have guest_id and event_id only
CREATE TABLE attends (
guest_id SMALLINT UNSIGNED NOT NULL
event ENUM('rehearsal', 'rehearsal dinner', 'ceremony', 'reception')
NOT NULL,
PRIMARY KEY (guest_id, event)
)
CREATE TABLE gifts (
guest_id SMALLINT UNSIGNED NOT NULL,
gift VARCHAR(100) NOT NULL,
type ENUM('cash', 'home', 'fun', 'sex-toy'),
price DECIMAL(5,2), # we can be optimistic, right?
PRIMARY KEY (guest_id, gift),
KEY type_k (type) # probably not needed
)
I think the needed queries should be pretty easy to get with this
setup. The basic idea when setting it up was, which data are one-to-one
with the guests, and which are one-to-many or many-to-many? If it's
one-to-one, you can just put it into the guests table. But many-to-many
data should be put into their own table.
Each event has many attendees, and each guest has many events to attend:
that's many-to-many. Each gift has one giver, but each guest can give
many gifts: one-to-many. The ENUM in the events table lets us cheat,
so even though it's many-to-many we can skip the events table, since its
info is stored in the ENUM type itself. But if you want any more info
about events besides just the name, then you have to split it off to a
separate table.
Let us know how it goes!
Tim
--
Tim Smith < tim@stripped > :MySQL Development Team: Boone, NC USA.
