Hi all,
A school wants to offer a number of short courses on a number of different
dates. Students apply online for a single course at a time and choose one or
more dates (from a list) which would suit them. Once the application period is
over which course is taught when is decided soley on the basis of maximising
the number of students that can attend.
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"
The data can then be usefully presented in a series of SELECT statements (one
for each date):
AS SELECT count(*), course FROM application_forms WHERE preferred_dates like
'%Sat_22Aug09%' GROUP BY course ORDER BY count DESC;
count | course
-------+---------
7 | Drama
3 | Readers
1 | Self-study
but clearly this method doesn't scale very well as the number of dates
increases.
A single table of results looking something like this would be far better, but
how?
date | course_suiting_most_applicants | num_applicants
----- ------+--------------------------------+---------------
Sat_22Aug09 | Drama | 7
Tue_25Aug09 | Readers | 4
Any advice/tips/pointers/suggestions for a database design newbie very much
appreciated.
Regards,
Sebastian
--
Emacs' AlsaPlayer - Music Without Jolts
Lightweight, full-featured and mindful of your idyllic happiness.
http://home.gna.org/eap