1. Original schema

1.1. Experiment database

1.2. List of available experiments (separate database schema)

2. Refined schema (step 1)

2.1. Experiment database

  • there is no intervals table anymore
  • redundant columns in entry table where removed
  • scheduler  table no longer keeps info related to MDSPlus  based notation of source data
  • entry_data contains column for holding complex data

3. Refined schema (step 2)

3.1. Experiment database

  • complex types are stored as real and imaginary
  • variables contain ids  in the name instead of cpo 

4. Refined schema (step 3)

4.1. Experiment database

  • scheduler table is now called request
  • all the plural names of tables were changed to singular ones
  • all the indexes are now unified (naming convention is the same across all the indexes)

5. Refined schema (step 4)

5.1. Experiment database

  • request table no longer holds URI for the data entry
  • there is a new table: experiment. This table contains location of data we are working with. As we may have multiple requests for a single experiment (e.g. update of the data) I want to keep URI in separate table.
  • entry table is now linked with request - this way it points to the experiment (via request)
  • There is a new table: replace - this table will contain information for tracking additional info (as described in Proposal on Metadata Schema Standardisation and Mapping Existing Schemas)
  • There is a new table: reference - this table will allow to store multiple DOI (maybe something else) related to a particular entry
  • There is a new table: tag - this table will contain all the tags defined by users
  • There is a new table: tag_entry - this table will contain all the links between: user, tag and entry; this way it will be possible to track who has tagged entry with given tag
  • Removed: machine table is no longer here. Do we really need it?

6. Refined schema (step 5)

6.1. Experiment database

  • auto increment for all ID fields in database
  • further unification of fields (all primary keys and foreign keys are represented solely by INT)
  • further cleaning of indexes

7. Refined schema (step 6)

7.1. Experiment database

  • all text fields are now represented by VARCHAR (it's still a subject to change)
  • using TEXT as type of column lead to strange issues in some of the SQL engines (e.g. H2)

8. Refined schema (step 7)

8.1. Experiment database

  • table replace was renamed to entry_replacement - by accident, we have used reserved word

9. Refined schema (step 8)

9.1. Experiment database

  • There is a new table: variable_type - it describes possible types of the variables
  • Inside variable table, there is no longer variable_type column. Instead there is a foreign key (variable_type_id) that points at variable_type.id
  • There is a new table: request_status - it describes possible statues of the request
  • Inside request table there is no longer status column - instead there is a foreign key (request_status_id) that points at request_status.id

10. Refined schema (step 9)

10.1. Filter table

  • There is a new table: filter - it allows to store custom user filters
  • There is a new table: user_filter - it allows to link filter with user; it is possible to share filter between different user - this is why filters require linking table between user and filter

11. Refined schema (step 10)

11.1. user_filter

  • There are fixes inside user_filter table
  • There is a new explicit ID column
  • relations are non-identifying (user - user_filter - filter)

12. Refined schema (step 11)

12.1. experiment_description

12.2. VARCHAR to TEXT

  • all text fields are now represented by TEXT fields



  • No labels