Database Design

The database will consist of three tables (1) users, (2) chores, and (3) chore assignments. For each user, we store name, address, email, amount of chorecoins, and password. For each chore, we store an identifier of the user who created the chore, the title of the chore, the description of the chore, the amount of chorecoins that will be received for completing the chore, and a due date for the chore. Chore assignments include an user id, an id for the chore, a timestamp when the assignment has been created, and a timestamp showing when the chore was completed.

As an image, the database schema looks as follows (created with dbdesigner.net):

Database schema with three tables: (1) users, (2) chores, and (3) chore assignments.

The corresponding SQL create table statements are as follows. For timestamp, we use timestamp with time zone. Further, we set the email as unique.

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name varchar(255) NOT NULL,
  address varchar(255) NOT NULL,
  email varchar(255) NOT NULL UNIQUE,
  chorecoins integer NOT NULL DEFAULT 0,
  password varchar(60) NOT NULL
);

CREATE UNIQUE INDEX ON users((lower(email)));

CREATE TABLE chores (
  id SERIAL PRIMARY KEY,
  user_id integer NOT NULL,
  title varchar(255) NOT NULL,
  description TEXT NOT NULL,
  chorecoins integer NOT NULL DEFAULT 0,
  due_date TIMESTAMP WITH TIME ZONE,
  CONSTRAINT fk_user_id FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE TABLE chore_assignments (
  user_id integer NOT NULL,
  chore_id integer NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE,
  completed_at TIMESTAMP WITH TIME ZONE,
  CONSTRAINT fk_user_id FOREIGN KEY (user_id) REFERENCES users(id),
  CONSTRAINT fk_chore_id FOREIGN KEY (chore_id) REFERENCES chores(id)
);