On Tue, Aug 18, 2009 at 08:58:12AM +0000, Sebastian Tennant wrote:
> Perhaps the simplest model is a database table 'application_forms' which
> includes two text columns; 'course' and 'preferred_dates' with entries that
> look like this:
>
> course: "Drama"
> prefered_dates: "Sat_22Aug09, Tue_25Aug09, Tue_08Sep09"
I'd use the standard "date" data type for storing dates in, it's much
more useful than text values. Then the conventional method of breaking
single (non-normalized) tables down into smaller normalized tables.
I've got a bit over the top here, but shows what could be done if this
was going to be a bigger database.
-- list of students, if you want
CREATE TABLE students (
student TEXT PRIMARY KEY,
name TEXT,
email TEXT
);
-- list of courses, again only if you want database to be able to check
-- that people are signing up for valid courses
CREATE TABLE courses (
course TEXT PRIMARY KEY,
runby TEXT,
description TEXT
);
-- which courses are available on which days
CREATE TABLE course_availability (
course TEXT REFERENCES courses,
date DATE,
PRIMARY KEY (course,date)
);
-- which students want to do which courses
CREATE TABLE application_forms (
student TEXT REFERENCES students,
course TEXT REFERENCES courses,
PRIMARY KEY (studentnum,course)
);
-- and on which days do they want to do them
CREATE TABLE application_preferred_date (
student TEXT,
course TEXT,
date DATE,
PRIMARY KEY (student,course,date),
FOREIGN KEY (student,course) REFERENCES application_forms,
FOREIGN KEY (course,date) REFERENCES course_availability
);
The only table that's really needed to solve your original problem would
be the last one, but the others provide all the checks that the data
is actually going in correctly and may or may not be useful depending
on your problem. The main thing to notice is lots of tables with few
columns, the reason being is that the database normally takes care of
the rows and you, the DBA/programmer, take care of the columns. Thus
the more work you can give to the database the better.
If my student number is 'cs1234' and I want to do a database course, I
would put in:
INSERT INTO application_forms (studentnum,course) VALUES
('cs1234','database 101');
INSERT INTO application_preferred_date (studentnum,course,date) VALUES
('cs1234','database 101','2009-08-26'),
('cs1234','database 101','2009-08-28'),
('cs1234','database 101','2009-08-31');
If I wanted to know how many people wanted to do each course on each
date, I'd just do:
SELECT course, date, COUNT(*)
FROM application_preferred_date
GROUP BY course, date;
Hope that gives you some ideas!
--
Sam http://samason.me.uk/
