Back to Table of Contents

Brain Teasers Database Design


These relations came fairly easily from my E/R diagram. I was lucky that after the conversion they were already in BCNF and 4NF, so I didn't have to do any tricky stuff to get them looking good.

teaser(id, title, category, body, hint, answer, numviews, diffvotes, funvotes, diffrating, funrating)
This relation will store all the information about the teasers including the votes and ratings for difficulty and fun-value. Here you can see one of the changes that occurred from the conversion. I decided to roll the ratings into this table instead of keeping them separate as in the E/R diagram because there was a one to one relationship and no duplication of data would occur by combining the tables into one.

account(username, password, numlogins, lastlogin, email, name)
This relation stores information about users. Usernames will be the key since they must be unique. Because email and name are not keys, it would be possible for people to have multiple accounts if they chose different usernames.

featured(fdate, id)
This small relation stores info about when certain teasers were featured on the front page of the web site.

submitted(id, username, submitdate, approveddate, approved)
This relation records teasers that are submitted by the users. It also tracks whether or not the teaser was approved. Only approved teasers appear on the web site, but the database remembers teasers that were not approved.

Functional Dependencies

Here are the functional dependencies that I came up with for my database.

id->title, category, body, hint, answer, numviews, diffvotes, funvotes, diffrating, funrating




id->submitdate, username, approved


Back to Table of Contents

by Jake Olefsky - June 2000