Proposed Changes to Ticket Data Model
tickets
| ticket_id | ticket_attrib_id | sequence | value
|
| 1 | 1 | 1 | Fix Blah Blah in X Module
|
| 1 | 2 | 1 | We really should fix Blah Blah in the X Module because it affects Y and Z as well as P, D, and Q. I can recreate it by doing so-and-so.
|
| 1 | 3 | 1 | 1 (Component 1)
|
| 1 | 4 | 1 | 2 (Version 2.0)
|
| 1 | 5 | 1 | 4 (Normal)
|
| 1 | 15 | 1 | Yeah, I see the problem...
|
| 1 | 15 | 2 | Fixed in [1]
|
ticket_attribs
| ticket_attrib_id | ticket_attrib_name | label | AllowMultiple? | AllowWiki? | UI_Widget
|
| 1 | Summary | Short Summary | N | N | text
|
| 2 | Description | Full Description | N | Y | textbox
|
| 3 | Component | Component | Y | N | select
|
| 4 | Version | Version | Y | N | select
|
| 5 | Severity | Severity | N | N | select
|
| 6 | Priority | Priority | N | N | select
|
| 7 | Milestone | Target Milestone | N | N | select
|
| 8 | Keywords | Keywords | N | N | text
|
| 9 | AssignedTo? | Assigned To | N | N | text
|
| 10 | CC | CC | Y | N | text
|
| 11 | Status | Status | N | N | text
|
| 12 | Resolution | Resolution | N | N | text
|
| 13 | ReportedBy? | Reported By | N | N | text
|
| 14 | ReportedDate? | Date | N | N | text
|
| 15 | Comment | Comment | Y | Y | textbox
|
ticket_attrib_values
| ticket_attrib_id | ticket_attrib_value_id | ticket_attrib_value
|
| 3 | 1 | Component 1
|
| 3 | 2 | Component 2
|
| 4 | 1 | Version 1.0
|
| 4 | 2 | Version 2.0
|
| 4 | 3 | Version 3.0
|
| 5 | 1 | Blocker
|
| 5 | 2 | Critical
|
| 5 | 3 | Major
|
| 5 | 4 | Normal
|
| 5 | 5 | Minor
|
| 5 | 6 | Trivial
|
| 5 | 7 | Enhancement
|
ticket_changes
| ticket_id | time | author | ticket_attrib_id | oldvalue | newvalue
|
| 1 | 1061788261 | brad | 3 | 1 | 2
|
The only real modification to the ticket_changes table is to rename field to ticket_attrib_id.
Notes
- I haven't thought about attributes that have modifiers themselves yet.
- Milestones have dates and descriptions
- Comments have dates and authors
Pros
- Standard Ticket Attributes and Custom Ticket Attributes are handled in same way
- no different handlers in code
- no need for trac.ini section for custom ticket attributes
- All attributes and attribute values have surrogate keys, so changes to the values themselves don't orphan any 'no-longer-valid' values.
- This format, coupled with metadata allows for simpler code to deal with the data model, most likely reduced lines of code, and table-driven extensions of the system that are implemented without any code changes or recompiles, etc.
- A very interesting feature of allowing multiple value, i.e. this bug is exhibited in multiple versions, or, for our company, I'd love to know that there are multiple customers (a custom field for us) interested in this ticket.
See Trac ticket #918
Cons
- The vertical structure of a ticket, and handling multi-row results for doing work with a ticket is unorthodox, but I believe, and am hoping, this could be handled in some sort of Python data structure.
- The tables do not self-describe very well, and developers are reliant on metadata to figure out how to relate and use tables. i.e. you can't tell much about a ticket in the tickets table from the table's field names
- You'll need an extra translation step to take human-readable queries and turn them into attribute ids. People will still want to write code and do administration referring to the field names.
- There's a performance hit for doing sql queries with constraints on multiple columns, such as "select * from tickets where owner='joe' and priority<2 and status='Open'" because you're doing self-joins on the table.
