Brain Teasers Database Design

Schema

create table teaser(
id int NOT NULL PRIMARY KEY,
title varchar(64),
category varchar(64),
body varchar2(500),
hint varchar(255),
answer varchar(255),
numviews int,
diffvotes int,
funvotes int,
diffrating real,
funrating real,
CHECK(diffrating >=0 AND diffrating <=10),
CHECK(funrating >=0 AND funrating <=10)
);

Above we see the teaser table. The id is defined as the key, and is required to be non-null. You'll see that the body is limited to 500 bytes. I had difficulty with getting fields larger than this to work properly using the drivers and software provided to my class, although varchar2 should support up to 4000 bytes of text. Also notice the constraints that the ratings must be between 0 and 10.

create table account(
username varchar(16) NOT NULL PRIMARY KEY,
password varchar(16),
numlogins int,
lastlogin date,
email varchar(64),
name varchar(64),
CHECK(numlogins >0)
);

A fairly simple conversion of the account relation. Notice the special date data type, and the constraint that numlogins be positive.

create table featured(
fdate date PRIMARY KEY,
id int REFERENCES teaser(id) ON DELETE cascade
);

The featured table contains a foreign key which references the real key in the teaser table. The cascade keyword means that if the teaser that this one references is deleted, this one will also be deleted.

create table submitted(
id int PRIMARY KEY REFERENCES teaser(id) ON DELETE cascade,
username varchar(16) REFERENCES account(username) ON DELETE cascade,
submitdate date,
approveddate date,
approved varchar(1) CHECK(approved IN('y','n','u')),
CHECK(submitdate <= approveddate)
);

This has two foreign keys and two constraints. I want the approved field to contain one of the 3 characters 'y','n', or 'u'. (u for undecided).

Back to Table of Contents

by Jake Olefsky - June 2000