Back to Table of Contents

Brain Teasers Database Design

Relations


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

username->password

email->name

username->email

id->submitdate, username, approved

fdate->id




Back to Table of Contents


by Jake Olefsky - June 2000