Relational Model for Trac

As outlined in ​my post to trac-dev, we should use the relational features of the database to arrive at clean, generic solutions. The first step would be to eliminate Ticket and Milestone from the model and focus on the common denominator: Work Efforts.

Source: The Data Model Resource Book Vol 3 p 178 (ISBN 978-0-470-17845-4)

Work Effort is a unit of work, like a ticket, project, milestone, timebox or similar entity that represent work with a start..end time (enumerated in Effort Type). Rows in Association table connect *any* two work efforts together with a specified (or not) relationship type + rule (enumerated in the right-hand tables). This allows very flexible aggregation, and we can support all kinds of evolving/plug-in relationships, breakdown structures and entity types without changing the model. This is not "normalized" per se, but the idea is to model the real entities and relationships.

The big problem with GenericTrac, is that the core of the model is a nonexisting entity (resource), in practice emulating the database system within itself. We'll end up reinventing relational algebra in middleware, bit by bit, to compensate for this fact; If you still think that will be simpler long-term than *using* the established algebra to work with data, I can only suggest you consider it carefully before starting ;-)

(it is possible to port the model to older versions using triggers)

SQL to consider (SQLite 3.6.19 or newer)

PRAGMA foreign_keys = ON;
BEGIN EXCLUSIVE;

-- custom field *data* from EAV system points here ("resource_prop")
CREATE TABLE WorkEfforts (
  effort_id     INTEGER PRIMARY KEY AUTOINCREMENT,
  effort_type   INTEGER,
  -- notice how useful these two date columns are within
  -- the "hierarchy" of work, when compared to ticket-milestone:
  from_date     JULIAN DEFAULT 0
    CHECK(from_date <= thru_date),
  thru_date     JULIAN DEFAULT 0
    CHECK(thru_date >= from_date),
  name          VARCHAR(128) NOT NULL,
  -- properties common to all work efforts here
  FOREIGN KEY(effort_type) REFERENCES WorkEffortTypes(type_id)
);

-- custom field *defs* from EAV system points here ("resource_schema")
CREATE TABLE WorkEffortTypes (
  type_id       INTEGER PRIMARY KEY AUTOINCREMENT,
  parent_id     INTEGER DEFAULT NULL,
  name          VARCHAR(128) NOT NULL,
  -- common properties and hints to middleware here.
  FOREIGN KEY(parent_id) REFERENCES WorkEffortTypes(type_id)
    ON UPDATE CASCADE
    ON DELETE SET DEFAULT
);

CREATE TABLE WorkEffortAssocs (
  from_eff_id   INTEGER NOT NULL REFERENCES WorkEfforts(effort_id),
  to_eff_id     INTEGER NOT NULL REFERENCES WorkEfforts(effort_id),
  assoc_type    INTEGER DEFAULT NULL,
  assoc_rule    INTEGER DEFAULT NULL,
  -- further details on the relationship here, when was it established,
  -- by who, for what time period is it valid etc.
  FOREIGN KEY(assoc_type) REFERENCES WorkEffortAssocTypes(type_id)
    ON UPDATE CASCADE
    ON DELETE CASCADE,
  FOREIGN KEY(assoc_rule) REFERENCES WorkEffortAssocRules(rule_id)
    ON UPDATE CASCADE
    ON DELETE CASCADE
);

CREATE TABLE WorkEffortAssocRules (
  rule_id       INTEGER PRIMARY KEY AUTOINCREMENT,
  name          VARCHAR(128) NOT NULL CONSTRAINT assocrule_name UNIQUE
  -- further hints to the middleware about business rules here
);

CREATE TABLE WorkEffortAssocTypes (
  type_id       INTEGER PRIMARY KEY AUTOINCREMENT,
  parent_id     INTEGER DEFAULT NULL,
  name          VARCHAR(128) NOT NULL,
  -- further hints to the middleware about business rules here
  FOREIGN KEY(parent_id) REFERENCES WorkEffortAssocTypes(type_id)
    ON UPDATE CASCADE
    ON DELETE SET DEFAULT
);

-- Test data
INSERT INTO WorkEffortTypes (name) VALUES ('Project');
INSERT INTO WorkEffortTypes (parent_id, name) VALUES (1, 'Phase');
INSERT INTO WorkEffortTypes (parent_id, name) VALUES (2, 'Task');

INSERT INTO WorkEffortAssocRules (name) VALUES ('Precedent');
INSERT INTO WorkEffortAssocRules (name) VALUES ('Concurrent');
INSERT INTO WorkEffortAssocRules (name) VALUES ('Complement');
INSERT INTO WorkEffortAssocRules (name) VALUES ('Substitute');

INSERT INTO WorkEffortAssocTypes (name) VALUES ('Work Breakdown');
INSERT INTO WorkEffortAssocTypes (name) VALUES ('Program');
INSERT INTO WorkEffortAssocTypes (name) VALUES ('Peer to Peer');

COMMIT;