Edgewall Software
Home
Trac
Trac Hacks
Genshi
Babel
Bitten
Home
Download
Documentation
Mailing Lists
License
FAQ
Search:
Login
Preferences
Help/Guide
About Trac
Wiki
Timeline
Roadmap
Browse Source
View Tickets
New Ticket
Search
Context Navigation
+0
Start Page
Index
History
Editing RelationalModel
Adjust edit area height:
8
12
16
20
24
28
32
36
40
Edit side-by-side
== Relational Model for Trac == As outlined in [trac-dev:6087 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. [[Image(workeffort.jpg)]] 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) == {{{ #!sql 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; }}}
Note:
See
WikiFormatting
and
TracWiki
for help on editing wiki content.
Change information
Your email or username:
E-mail address and name can be saved in the
Preferences
Comment about this change (optional):
Attachments
(1)
workeffort.jpg
(
46.9 KB
) - added by
tracdev@…
14 years ago
.
Download all attachments as:
.zip
Note:
See
TracWiki
for help on using the wiki.