RemindMe: Programming with PHP and Mysql

Web Development with PHP and Mysql

Development of the RemindMe Service

Data Modelling

Before writing any code you must understand what the data model looks like. Going back to the ArsDigita case study, we find an Oracle data model of:


--
-- this table has one row for each person using the system
-- the PRIMARY KEY constraint says that there can't be two
-- rows with the same value in the EMAIL column
--

create table bday_users (
	email		varchar(100) primary key,
	password	varchar(100) not null
);

create sequence reminder_id_sequence start with 1;

create table bday_reminders (
	reminder_id		integer primary key,
	email			varchar(100) references bday_users,
	event_description	varchar(400),
	event_date		date,
	remind_week_before_p	char(1) check (remind_week_before_p in ('t','f')),
	remind_day_before_p	char(1) check (remind_day_before_p in ('t','f')),
	remind_day_of_p		char(1) check (remind_day_of_p in ('t','f')),
	last_reminded		date
);

create index bday_reminders_idx on bday_reminders(email);

Other than converting this to Mysql, there are some other changes I would like to make.

Rather than use the email address as the primary key for the bday_users table, I'll create a unique id (user_id) for the primary key. The benefit to this approach is that it is much easier to change the email address that the user is notified at. With the email address as a primary key, changes would have to be made to both tables.
I'd like the event_date to consist of three separate fields, one for month, one for day, and one for year. My reasoning behind this is that it will allow you to leave the year blank if you do not know it. If you do know, the email reminder can also tell you how old the person will be/how long they have been married.
I'd like to see when people signed up for the service, so I'll add a created_date field to the bday_users table.
I'm going to store an md5 hash of the password in the user_password column, so I need a column that is at least 32 characters wide, because the md5 function in PHP always returns 32 characters.
Mysql does not support foreign keys, so we will not have a database-enforced reference from the bday_reminders to the bday_users table.

After making these modifications, adding the database creation commands, and converting the scripts to Mysql, I end up with this code:

create database remindme;

grant all on remindme.* to remindme identified by "my_password";

grant all on remindme.* to remindme@localhost identified by "my_password";

use remindme;

DROP TABLE IF EXISTS bday_users;

CREATE TABLE bday_users(
	user_id 			INT unsigned auto_increment not null,
	user_email 			VARCHAR (250) NOT NULL,
	user_password 		VARCHAR (35) NOT NULL,
	created_date 		DATE,
	UNIQUE (user_id),
	PRIMARY KEY (user_id),
	KEY (user_email)
);

DROP TABLE IF EXISTS bday_reminders;

CREATE TABLE bday_reminders(
	reminder_id         INT unsigned auto_increment not null,
	user_id             INT unsigned not null,
	event_description   VARCHAR (250),
	event_date_month    TINYINT unsigned default 0,
	event_date_day      TINYINT unsigned default 0,
	event_date_year     SMALLINT unsigned default 0,
	remind_week_before  CHAR (1) default 'Y',
	remind_day_before   CHAR (1) default 'Y',
	remind_day_of       CHAR (1) default 'Y',
	last_reminded       DATE,
	UNIQUE (reminder_id),
	PRIMARY KEY (reminder_id),
	KEY (user_id)
);

Previous | Next