table: chore
This is the most important table.
Each row in this table represents a distinct task that must be completed.
For instance, it lists a specific person's haircut and not just "haircut".
DROP TABLE IF EXISTS chore;
CREATE TABLE chore (
id int(10) unsigned NOT NULL AUTO_INCREMENT,
name varchar(45) NOT NULL,
description varchar(255) NOT NULL,
lastCompletedOn date NOT NULL,
everyXdays int(10) unsigned NULL,
everyday bit(1) NULL,
monday bit(1) NULL,
tuesday bit(1) NULL,
wednesday bit(1) NULL,
thursday bit(1) NULL,
friday bit(1) NULL,
saturday bit(1) NULL,
sunday bit(1) NULL,
makesYouSweat bit(1) NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
table: person
This table lists the people who might have chores assigned to them.
DROP TABLE IF EXISTS person;
CREATE TABLE person (
id int(10) unsigned NOT NULL AUTO_INCREMENT,
name varchar(45) NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
table: chore2person
This table assigns chores to people.
A single chore may be assigned to more than one person.
If so, the chore will be listed in more than one person's list of chores to be done, and it will disappear from them all once the first person gets to it.
DROP TABLE IF EXISTS chore2person;
CREATE TABLE chore2person (
choreid int(10) unsigned NOT NULL,
personid int(10) unsigned NOT NULL,
skipped bit(1) NULL,
procrastinated datetime NULL,
makesYouSweat bit(1) NULL,
onHold bit(1) NULL,
severity int(10) NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
table: category
This table lists categories that chores might be put into, mostly for display purposes.
DROP TABLE IF EXISTS category;
CREATE TABLE category (
id int(10) unsigned NOT NULL AUTO_INCREMENT,
name varchar(45) NOT NULL,
priority int(10) unsigned NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
table: chore2category
This table puts a chore into a category, mostly for display purposes.
DROP TABLE IF EXISTS chore2category;
CREATE TABLE chore2category (
choreid int(10) unsigned NOT NULL,
categoryid int(10) unsigned NOT NULL,
displayOrder int(10) unsigned NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
logic: describing frequency
The `chore` table has three ways of describing how often a chore must be performed.
(1)
Some chores need to be performed every day.
For these, the `everyday` bit field is set.
(2)
Other chores need to be performed on certain days of the week, such as weekdays or weekend days.
For these, there are seven bit fields corresponding to each day of the week.
(3)
Most chores, however, don't need to be performed that often.
For these, the `everyXdays` field is used to represent how often (in days) the task needs to be performed.
logic: determining due date
Each chore has a `lastCompletedOn` date field.
This field is used to determine if the chore is due.
If the due date is in the past, then the chore is due.
When the chore is completed, the `lastCompletedOn` field is updated with today's date.