Developer's guide Database schema

28 mai 2024 à 05:35

Database schema

Database schema as designed in the specifications

Note

This database schema doesn’t really represent what has been implemented in the database.

In the fields table, external_source_id, external_source_field_id, comparator_id, are never used as for now.

Here are the details of what they were planned for:

  • external_source_id (varchar(50)) : external source id defined in the configuration file. Example : graf. Is defined on fields of type template.
  • external_source_field_id (varchar(50)) : field id of the field to be imported from the external source. Is defined on fields of type template.
  • comparator_id (int) : Only if the field_type = quantitative_addition_compared or quantitative_average_compared in order to indicate another field’s id with which to compare the value. Used to compare the value of a quantitative field with the value of an objective field.

And is_focused, focused_by columns are not even currently present in the database. They were planned to manage realtime collaboration in order to lock fields when focused and to register who is writing on them.

Note concerning data_versions

data_versions table has been created to manage fields versions and to allow rollbacks and other historical functions. However as for now, field versioning has not been implemented. This data_versions table only adds a layer of complexity without any benefits comparing to a simple value column in the fields table. It has been implemented from the beginning in order to prevent a future data migration.

Dbdiagram.io syntax

Here below you can see the syntax interpreted by dbdiagram.io to generate the upper schema.

Table fields {
  id int
  name varchar
  parent_id int
  template_id int
  external_source_id varchar
  external_source_field_id varchar
  field_type varchar
  data_id int
  comparator_id int
  is_focused timestamptz
  focused_by timestamptz
}

Table data_versions {
  id int
  author varchar
  date date
  value varchar
  field_id int
 }

Table policies_groups {
  id int
  comment varchar
  created_at date
  updated_at date
 }

Table policies_groups_policies {
  policy_id int
  policies_group_id int
}
 
Table policies {
  id int
  report_id text
  field_id text
  actions text[]
  field_name text
  field_data text
}	
// Creating references
// You can also define relaionship separately
// > many-to-one; < one-to-many; - one-to-one; <> many-to-many
Ref: fields.data_id - data_versions.id
Ref: data_versions.field_id > fields.id
Ref: policies_groups_policies.policy_id <> policies.id
Ref: policies_groups_policies.policies_group_id <> policies_groups.id