From: Peter Brawley
Date: May 24 2006 10:52pm
Subject: Re: Fun with Dates and Incentives.
List-Archive: http://lists.mysql.com/mysql/198276
Message-Id: <4474E3A1.9030005@earthlink.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Brian,

Somehow the server ate my response...

Re your schema,
  --it's redundant to define PRIMARY and UNIQUE keys on the same column,
  --why not an INT student id?
  --what if two (eg married) students share an email account?
  --comparing datetimes across multiple time zones will be simpler if you  
    set completed_modules.time=UTC_TIMESTAMP in each new row of that table.

That would give ...

CREATE TABLE students (
  id INT NOT NULL,        -- auto_increment [simplest] or assigned by 
school?
  email varchar(64) NOT NULL,
  fname varchar(32) NOT NULL,
  lname varchar(32) NOT NULL,
  role char(2) NOT NULL default '5',
  password varchar(8) NOT NULL,
  phone varchar(24) default NULL,
  reg_date date default NULL,
  PRIMARY KEY (id),
  KEY email (email)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE completed_modules (
  id INT NOT NULL,
  module_id char(2) NOT NULL default '',
  score INT NOT NULL default 0,
  time timestamp NOT NULL default CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

To find the first 10 scores of 100 on a particular module, just ...

SELECT
  CONCAT(s.lname,', ',s.fname) AS Name,
  c.time,
  c.score
FROM students s
INNER JOIN completed_modules c USING (id)
WHERE c.module_id = 1 AND c.score = 100
ORDER BY c.time ASC
LIMIT 10;

PB




-- 
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.394 / Virus Database: 268.7.1/347 - Release Date: 5/24/2006