how to structure database?

dshallenberger

I need to setup a database to handle an essay contest website. Kids will go to this site and write a short essay about a pre-defined topic, and after the contest ends, a winner will be chosen.

I need to store all the usual contact information, as well as the essay itself. Plus, I need to have a status field showing 'active', 'pending', 'waiting' or 'inactive'. Because I'm dealing with kids under 18, everything needs to be opt-in and COOPA compliant.

What is the best way to structure the tables in my database?

I could go with all the data in one table, but I know that is not the most efficient. How about if I broke it into 3 tables: 1) all the contact info... 2) the essays... 3) everyones status.

What do you recommend?

Thanks much for any help!

Dan

rachel2004

It depends what columns make up the three tables that you are suggesting.

In my opinion, it would be best to create a list of all possible columns that you'd want:

first name,
last name,
record created date,
city,
state,
etc.

You should have a pretty long list. While you're doing it, think to yourself which table would you logically conclude to contain those columns. For example, first name & last name would go into a table perhaps called "Users" whereas a column like city and state could go into "Addresses".

Keep the table names broad because there is no point in creating a "HomeAddresses" table and then a "WorkAddresses" table and then a "MailingAddresses" table. Instead create an "Addresses" table that captures any of those type of addresses and possibly create an extra column in that table called "location" which you can use as a flag: an "h" inserted into the "location" column could indicate that the record is a "home" address, a "w" for work, maybe a "b" for both (for those work at home), and maybe "m" for "mailing address". Do you see how that is better than creating a seperate table for each type of address?

Do the same thing for your "Users" table. Actually, I prefer the name "People" instead of "Users" because "Users" describes a type of role, whereas "People" is more broad and I can capture records for "visitors" (v), "anonymous" (a), "subscribers" (s), and in your cases maybe "parent" (p) or "child" (c).

Hope this helps.

dshallenberger

Ok, I have read before that keeping large numbers of fields in 1 table can be bad, and you seem to support that.

What would be the benefit to have a table called say 'users' and storing the first and last name, then a separate table called 'addresses' and storing all of the addresses in there, as opposed to putting all the names and addresses in one table?

I'm somewhat new to filling a database with more than very generic info, so setting up multiple tables and using joins is a new idea for me.

Here are the fields I need to capture and save:
date entered
first name
last name
address
city
state
zip
country
phone
email
email for parent
age
essay
status

Thanks for the quick reply!

Dan

nemonoman

Your assumption is somewhat faulty: It's OK to have lots of fields in a table. Mostly what you don't want to do is to duplicate data.

For example, I have a table called publicationAdTemplate with probably 40 or 50 fields. It has a single key field: publicationid

I can quickly find all the ad template records for a publication, and quickly digest them to create ads. Note that even though the record has fifty fields, most fields are a single Tinyinteger of length 1 or 2. So each record is total about 200 bytes.

What you really want to do is create appropriate relationships:

For example if your customers might have more than 1 address (bill to, ship to , warehouse, office, etc.) You'd do well to store those addresses individually in a separate table, referenced to the customer id.

One other element: If you have long text or blob type data, it can be better to store those elements in a separate table.

For example: You store whole books by chapter.

Rather than a table like 'BookChapter'
ID
Title
Author
Chapter
Text

You might to do well to have 2 tables:

BookChapterInfo

ID
Title
Author
Chapter

BookChapterText

BookChapterID
Text

etc.

dshallenberger

Ok, makes sense now! Thanks for the lengthy explanation... rachel2004, I went back and read your post after reading nemonoman's, and now I understand why you were breaking out the addresses.

I will only have only one address per entry, and only one entry per person. So, I can store the essays themselves in separate table from the contact table, cross-referenced to the entry id from the contact table, and just use a join to collect the data simultaneously.

Perfect! Thanks so much for the advice. I appreciate you both taking time to help me.

Dan