== The Model ==

Here's what could be the new model.

In the following:
 - ''int'' stands for the integer column type
 - ''string'' stands for a short text column type (e.g. varchar(65))
 - ''text''  stands for a long text column type, of unbounded size

=== Minimal Model ===
 - surrogate keys for all resources
 - only text fields


{{{
#!div style="width: 30%; border: 2px dotted #eee;  padding: 0 4em 1em 1em; float: left"

==== Schema ====

'''resource_schema'''
||= realm ||= prop ||= metaprop ||= value ||
|| string || string || string || text ||

'''resource_prop'''
||= id ||= prop ||= value ||= seq ||
|| int || string || text || int ||

'''resource_revprop'''
||= changeid ||= revprop ||= value ||= seq ||
|| int || string || text || int ||

'''resource_change'''
||= id ||= changeid ||= prop ||= value ||= seq ||
|| int || int || string || text || int ||
}}}
{{{
#!div style="width: 50%; border: 2px dotted #ddd; padding: 0 0 1em 1em ; float: right;"

==== Example Dataset ====

'''resource_schema'''
||= realm ||= prop ||= metaprop ||= value ||
|| ticket    || summary     || type     || text ||
|| ticket    || description || type     || wiki ||
|| ticket    || reporter    || type     || text ||

'''resource_prop'''
||= id ||= prop ||= value ||= seq ||
|| 0||   id      ||   130     || 0||
|| 0|| summary   ||Multiple Project Support|| 0||
|| 0||description||One day... || 0||
|| 0|| reporter  ||   joe     || 0||

'''resource_revprop'''
||= changeid ||= revprop ||= value ||= seq ||
|| 1|| author  ||    joe    || 0||
|| 1|| date    ||5 years ago|| 0||
|| 2|| author  ||    joe    || 0||
|| 2|| date    ||2 years ago|| 0||
|| 2|| comment || come on...|| 0||
|| 3|| author  ||   cboos   || 0||
|| 3|| date    ||1 year ago || 0||
|| 3|| comment || sure...   || 0||

'''resource_change'''
||= id ||= changeid ||= prop ||= value ||= seq ||
|| 0|| 1|| id        ||130|| 0||
|| 0|| 1|| summary   ||Multiple Project Support|| 0||
|| 0|| 1||description||Should be easy...|| 0||
|| 0|| 1|| reporter  ||joe|| 0||
|| 0|| 2||description||Should be easy... Redmine has it! || 0||
|| 0|| 3||description||One day...|| 0||
}}}

[[html(<br style="clear: both" />)]]


=== Intermediate Model ===
 - surrogate keys for all resources
 - text and int fields (same as [#CompleteModel Complete Model], without the `*_string` tables)

The [#MinimalModel minimal model] above is handy for showing the essence of the new model,
but it's too simple in practice.

I think we need at the very least to support 'integer' type columns, useful for storing dates efficiently, boolean values, and relations to other resources (as the surrogate ''id''s will be integers).


=== Complete Model ===
 - surrogate keys for all resources
 - int, bigint, short and long text fields

Not absolutely necessary to go that far, this could nevertheless help a lot for the MySQL backend (#6986), possibly also for a future Oracle backend. Don't know about PostgreSQL, but for SQLite this should be indifferent.

`bigint` is needed since we use this to store microsecond precision timestamps (#6466).

{{{
#!div style="width: 30%; border: 2px dotted #eee;  padding: 0 4em 1em 1em; float: left"

==== Schema ====

'''resource_schema'''
||= realm ||= prop ||= metaprop ||= value ||
|| string || string || string || text ||


'''resource_prop'''
||= id ||= prop ||= value ||= seq ||
|| int || string || text || int ||

'''resource_revprop'''
||= changeid ||= revprop ||= value ||= seq ||
|| int || string || text || int ||

'''resource_change'''
||= id ||= changeid ||= prop ||= value ||= seq ||
|| int || int || string || text || int ||


'''resource_prop_string'''
||= id ||= prop ||= value ||= seq ||
|| int || string || string || int ||

'''resource_revprop_string'''
||= changeid ||= revprop ||= value ||= seq ||
|| int || string || string || int ||

'''resource_change_string'''
||= id ||= changeid ||= prop ||= value ||= seq ||
|| int || int || string || string || int ||


'''resource_prop_int'''
||= id ||= prop ||= value ||= seq ||
|| int || string || int || int ||

'''resource_revprop_int'''
||= changeid ||= revprop ||= value ||= seq ||
|| int || string || int || int ||

'''resource_change_int'''
||= id ||= changeid ||= prop ||= value ||= seq ||
|| int || int || string || int || int ||

}}}
{{{
#!div style="width: 50%; border: 2px dotted #ddd; padding: 0 0 1em 1em ; float: right;"

==== Example Dataset ====

**FIXME** add example of multivalued property

'''resource_schema'''
||= realm ||= prop ||= metaprop ||= value ||
|| ticket || summary || type || text ||
|| ticket || description || type || wiki ||
|| ticket || reporter || type || string ||

----

'''resource_prop'''
||= id ||= prop ||= value ||= seq ||
|| 0|| summary   ||Multiple Project Support|| 0||
|| 0||description||One day... || 0||

'''resource_revprop'''
||= changeid ||= revprop ||= value ||= seq ||
|| 2|| comment  || come on...|| 0||
|| 3|| comment  || sure...   || 0||

'''resource_change'''
||= id ||= changeid ||= revprop ||= value ||= seq ||
|| 0 || 1||  summary  ||Multiple Project Support|| 0||
|| 0 || 1||description||Should be easy...|| 0||
|| 0 || 2||description||Should be easy... Redmine has it! || 0||
|| 0 || 3||description||One day...|| 0||

----

'''resource_prop_string'''
||= id ||= prop ||= value ||= seq ||
|| 0|| reporter || joe || 0||

'''resource_revprop_string'''
||= changeid ||= revprop ||= value ||= seq ||
|| 1|| author || joe    || 0||
|| 2|| author || joe    || 0||
|| 3|| author || cboos  || 0||

'''resource_change_string'''
||= id ||= changeid ||= prop ||= value ||= seq ||
|| 0|| 1|| reporter ||joe|| 0||

----

'''resource_prop_int'''
||= id ||= prop ||= value ||= seq ||
|| 0 || id || 130 || 0 ||

'''resource_revprop_int'''
||= changeid ||= revprop ||= value ||= seq ||
|| 1||   date   ||5 years ago|| 0||
|| 2||   date   ||2 years ago|| 0||
|| 3||   date   ||1 year ago || 0||

'''resource_change_int'''
||= id ||= changeid ||= prop ||= value ||= seq ||
|| 0|| 1|| id ||130||0||

}}}

[[html(<br style="clear: both" />)]]


== Discussion 

 - (cklein) [=#JBPM-approach] Why not implement all of the different `resource_prop*` tables into a single table, where each tuple has multiple attributes, see for example the JBPM datamodel for a working and presumably also fast approach. Here, there exists a process_variable or some similar table that stores all the different value types in single table. 
   - (cboos) not sure how you see that as an advantage; each row will waste all the fields but one; there need to be one index for each type, each index having to deal with lots of NULL values, each update will have to rebuild all indexes, etc.). But it could be worth benchmarking anyway...
  The schema would be like so:
{{{
table resource_prop
{
id
prop
type
int_val
string_val
datetime_val
...
}

table resource_revprop
{
change_id
prop
type
int_val
string_val
...
}

table resource_change
{
id
change_id
prop
type
int_val
string_val
...
}
}}}
 - And please rename the ''name'' field to ''prop'' so that it matches the one in the resource_schema table.
   - (cboos) done - now I use `prop` consistently to talk about resource property keys, `revprop` to talk about change property keys and `metaprop` in the schema (as those are properties of properties)
 - Also I would like to have the resource_schema table extended so that it will support different schemas for, say, different ticket types. That way, users can define their personal ticket type schemas. Of course, derivation would also be nice, but that could be implemented at a later point in time, requiring yet another table. That way we could have both inheritance at the schema level and also multiple different models per realm ;)
  {{{
table resource_schema
{
  realm
  urn    -- the urn of the ticket schema or wiki page schema to which this belongs to, defaults are for example trac::ticket or trac::milestone or trac::wiki-page and so on
  prop
  name
  type
}
  }}}
  The inheritance table for the schemas would then be
  {{{
table resource_schema_inheritance
{
  realm
  urn
  base -- the urn of the base schema
}
  }}}
   - (cboos) Interesting idea, though for simplicity my initial idea was that one could simply define new realms (bug: vs. enhancement:, both nevertheless managed by the !TicketModule)
 - inheritance would then provide for also multiple inheritance
   - (cboos) much harder ;-)