Yatish SQL schema

The relations between yatish tables are best described with a graph:

In the graph above, primary keys are underlined and foreign keys are written in italics.

SQL foreign key constraints are rather strictly applied:

• all foreign keys are compulsory (NOT NULL); however if you don't need to document the tool used during an activity (for example), then just create an empty record in the tool table and select it for your activity;
• ON DELETE CASCADE is invoked on table creation: thus if one deletes a row in, say, the tool table then any row which refers to this record in the activity table will also be deleted.

Yatish tables are prefixed with yatish_: it is possible to share a database with other applications.

The sync field exists only in the (local) SQLite database:

S	(synchronized)	the record has been inserted/updated in the (remote) MySQL table
I	(inserted)	the record was inserted locally but not yet in the remote table
U	(updated)	the record was modified locally and needs to be updated in the remote table
D	(deleted)	the record has been deleted locally and but not yet in the remote table

File/Update, if successful, will bring all SQLite sync values do S.

The SQL code used to create the local tables is listed here for reference:

CREATE TABLE yatish_client (
  id INTEGER,
  name TEXT NOT NULL UNIQUE,
  sync CHAR NOT NULL,
  PRIMARY KEY (id)
);
CREATE TABLE yatish_project (
  id INTEGER,
  name TEXT NOT NULL UNIQUE,
  client_id INT NOT NULL,
  sync CHAR NOT NULL,
  PRIMARY KEY (id),
  FOREIGN KEY (client_id) REFERENCES yatish_client (id)
    ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE yatish_task (
  id INTEGER,
  name TEXT NOT NULL UNIQUE,
  sync CHAR NOT NULL,
  PRIMARY KEY (id)
);
CREATE TABLE yatish_tool (
  id INTEGER,
  name TEXT NOT NULL UNIQUE,
  sync CHAR NOT NULL,
  PRIMARY KEY (id)
);
CREATE TABLE yatish_activity (
  id INTEGER,
  project_id INT NOT NULL,
  task_id INT NOT NULL,
  tool_id INT NOT NULL,
  sync CHAR NOT NULL,
  PRIMARY KEY (id),
  UNIQUE (project_id,task_id,tool_id),
  FOREIGN KEY (project_id) REFERENCES yatish_project (id)
    ON DELETE CASCADE ON UPDATE CASCADE,
  FOREIGN KEY (task_id) REFERENCES yatish_task (id)
    ON DELETE CASCADE ON UPDATE CASCADE,
  FOREIGN KEY (tool_id) REFERENCES yatish_tool (id)
    ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE yatish_timeslot (
  id INTEGER,
  start TEXT,
  stop TEXT,
  activity_id INT NOT NULL,
  sync CHAR NOT NULL,
  PRIMARY KEY (id),
  FOREIGN KEY (activity_id) REFERENCES yatish_activity (id)
    ON DELETE CASCADE ON UPDATE CASCADE
);