Re: Best database model for canvassing (and analysing) opinion - Mailing list pgsql-general

From Sam Mason
Subject Re: Best database model for canvassing (and analysing) opinion
Date
Msg-id 20090818111721.GO5407@samason.me.uk
Whole thread Raw
In response to Best database model for canvassing (and analysing) opinion  (Sebastian Tennant <sebyte@smolny.plus.com>)
Responses Re: Best database model for canvassing (and analysing) opinion  (John R Pierce <pierce@hogranch.com>)
List pgsql-general
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/

pgsql-general by date:

Previous
From: Ivan Sergio Borgonovo
Date:
Subject: Re: design, plpgsql and sql injection in dynamically generated sql
Next
From: Alban Hertroys
Date:
Subject: Re: multiple paramters in aggregate function