144 | | If this is the case, the '''resource_prop''' table could as well become simply: |
145 | | ||= id ||= prop ||= value ||= //seq// || |
146 | | |
147 | | - the ''realm'' information could simply be stored as another prop/value entry (`'realm'`,`<the resource realm>`) |
148 | | - this could be extended naturally to the ''support of multiple projects'', with another (`'project'`,`<projectid>`) prop/value pair; this is better as adding an additional `project` column to all entries |
149 | | |
150 | | Note that having a separate table for storing the ''relations only'' wouldn't necessary be better, as we would likely have to store something like: |
151 | | ||= role ||= source ||= target ||= seq || |
152 | | |
153 | | which is just a reformulation of: |
154 | | ||= prop ||= id ||= value ||= seq || |
155 | | |
156 | | i.e. the proposed '''resource_prop'''. |
| 147 | ==== Relations |
| 148 | |
| 149 | Storing the relations between resources is also an interesting topic. |
| 150 | We face several alternatives. Let's take the example of //ticket// and //project//. This is a simple example because we only have a "has_a / part_of" relation between ticket and project. |
| 151 | 1. in the `ticket_prop_int` table, via the `('project', <project_id>)` prop/value pair(s) (support of multiple projects per ticket is possible as any other multivalued field via multiple `seq`) |
| 152 | 2. in a dedicated `ticket_project` relation table `(<project_id>, <ticket_id>)` |
| 153 | 3. in a global `project_relation` table `(<project_id>, <resource_id>)` |
| 154 | 4. as part of the `project_prop_int` table, via `('ticket', <ticket_id>)` prop/value pair(s). This is symmetrical to 1., this time the relations are store on the project side. |
| 155 | |
| 156 | We also have the more demanding example of generic //ticket// vs. //ticket// relations, as discussed in #31. |
| 157 | There we don't want to have to add a new table for each new type of relation, hence a solution of the type 1. / 4. would be more appropriate (3. + a "role" type would essentially be the same as 1. / 4.). |
| 158 | |
| 159 | In order to decide between 1. or 4., we need to consider the nature of the relation, whether it's an essential or accidental property. |
| 160 | |
| 161 | A ticket doesn't really stand on its own, normally it's part of one project. Remember, in the context of MultiProject, even when there's no project defined, there's the implicit "whole" project. A ticket is eventually part of multiple projects but that's less common, if allowed at all. Even in that case, we can still consider that the //project// properties are essential properties of the ticket. |
| 162 | On the opposite, a project can well exist without any tickets attached to it, so it's pretty clear that the tickets are not essential properties of a project. It's also clear that when we load a project instance, we don't necessarily want to load thousands of ticket properties. |
| 163 | |
| 164 | For other relations, it can be less clear cut, we have to take sides. For example, concerning the ticket relations: |
| 165 | - ''**parent**/child'': a child ticket necessarily has a ''parent'' ticket, otherwise it's not a child ticket ; the parent ticket may have one or more children tickets, but it can also exist on its own |
| 166 | - ''**blocked-by**/is-blocking'': a ticket can be ''blocked-by'' one or more tickets ; the converse relation ''is-blocking'' is less strong |
| 167 | - ''**duplicate-of**/duplicated-by'': a ticket may be qualified to be a ''duplicate-of'' of another ticket ; that other ticket is ''duplicated-by'' one or many tickets, but this is less strong |