List:General Discussion« Previous MessageNext Message »
From:Brian Menke Date:May 24 2006 4:50pm
Subject:Fun with Dates and Incentives.
View as plain text  
I'm hoping for some general advice on an approach for the following
scenario:

 

I have a customer who wants to put an incentive program in place for
students taking learning modules and then completing tests. The concept is
simple. Award the first 10 people who complete a test with a score of
100%... that type of thing. Students are allowed to take test more than
once. Track each time the student takes the test and show the latest score
ect. You get the idea. I have the database tables and relationships already
all set up for the tests, but it's the tracking of the dates and times that
I don't have and it got me thinking.

 

I need to track down to the day/hour/minute level. Okay, that should be easy
(I think). I'm going to need to do a lot of date/time calculations. Would it
be best just to have a default of CURRENT_TIMESTAMP set for a TIMESTAMP
field? Or, is their something else I should be using? I have limited
experience having to munge and crunch date/time info and I want to make sure
I have the flexibility to do what I need in the future.

 

The next gotcha I thought up is what about different time zones. Obviously
without this consideration, people on the East coast would have an unfair 3
hour advantage over people on the west coast. I guess I can have a time zone
field in my student table so I could derive the time difference. Any
suggestions on a good time zone approach?

 

Here are my two tables as they stand now. I'm wondering if these are set up
in a way to allow me to do all this date time crunching I'm going to need to
do in the future? Any suggestions are greatly appreciated :-)

 

 

CREATE TABLE `students` (

  `store_id` varchar(6) NOT NULL,

  `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  (`email`),

  UNIQUE KEY `email` (`email`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

 

CREATE TABLE `completed_modules` (

  `module_id` char(2) NOT NULL default '',

  `email` varchar(64) NOT NULL,

  `score` int(2) NOT NULL default '0',

  `time` timestamp NOT NULL default CURRENT_TIMESTAMP

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

 

Brian Menke

Visual Matter, Inc

1445 Foxworthy Ave., Suite 50-215

San Jose, CA 95118

408 375 9969

 

San Jose ~ Los Angeles
www.visualmatter.com 

 


Thread
Fun with Dates and Incentives.Brian Menke24 May
  • Re: Fun with Dates and Incentives.Jason Dimberg24 May
  • Re: Fun with Dates and Incentives.Peter Brawley24 May
    • RE: Fun with Dates and Incentives.Brian Menke24 May
      • Re: Fun with Dates and Incentives.Peter Brawley25 May
        • RE: Fun with Dates and Incentives.Brian Menke25 May
          • Re: Fun with Dates and Incentives.sheeri kritzer25 May
            • RE: Fun with Dates and Incentives.Brian Menke25 May
              • Re: Fun with Dates and Incentives.Peter Brawley25 May
              • Re: Fun with Dates and Incentives.sheeri kritzer26 May
      • Re: Fun with Dates and Incentives.Jeff Shapiro26 May
      • Re: Fun with Dates and Incentives.Jeff Shapiro26 May
  • Re: Fun with Dates and Incentives.Peter Brawley25 May
RE: Fun with Dates and Incentives.George Law24 May