Data model review
Hi @lathrops1
The attached PDF shown the data model design for the NCBI dev team to review. @John.kopanas has provided the explanation below. Once your team has engaged with this I suggest we have a call to over over any queries.
The following is an explanation of the data modelling of the app:
The main Entities of the app are Organizations, Collections, Books, Divisions, Book Components, and Files.
Organizations Table
In this table each row describes an organization.
Looking at the Organizations table we see the main properties of an org (name
, email
, agreement
)
Collections Table
In this table each row describes a collection.
Looking at the Collection table we see the main properties (name
, status
, metadata
)
Status describes the status for all of the books in the collection
Metadata is a JSON data type, consisting of all the metadata of a collection
Book Table
In this table each row describes a book and its relationships.
Looking at the Book table we see the main properties of the book (title
, settings
, metadata
, workflow
, status
).
There are two fields (edition
, parent_id
) for keeping the relationship between a book and a new edition of the book. At the parent_id
we save the id of the original book and for edition
we keep the actual edition of that book.
Finally we see the relations with Collections, one Organization and Divisions. The collections field is an array of Collection ids. Divisions field is an array of the Divisions ids.
Divisions Table
In this table each row describes the Divisions per book. The divisions are FrontMatter, Body, and BackMatter which contain the book_components and the order of these components in the array as ids of book components.
Book Components Table
In this table each row describes a Book Component. This refers to any individual part of the book (such ‘Introduction’, ‘Chapter 1’, ‘Addendum A’, etc.). Each component can have its own metadata. Each component has a book-id
and the current status of the chapter (for example: review, published, etc.)
Book Component Versions Table
In this table we keep the multiple versions of the Book Components. Each record in the Book Component Table can have multiple records in the Book Component Versions Table describing the versions which can be either new versions because of a new uploaded Book Component file or a new published versions. Another important thing here is the file_id
, file_converted_id
fields, those fields keep the relation between the actual file of the specific version and the specified Book Component version. Also we see an is_published
field which we will use to mark which versions are published, we can also combine this with the updated
field of the table to keep track of the order of publishing.
File Table
In this table we keep the files of the whole app. Those files could be anything from the book cover to the version files of a Book Component. These can be distinguished by the category
field. Also we see some important properties for a file like mimeType
, size
, path
.
Users Table
In this table we keep all the users of the Application. With their properties surname
, given_name
, username
, email
, password
. The admin
filed specifies whether this user is an admin of the whole system. However we need a way to keep track of users that belong to an organisation: this is achieved with the Team Members table.
Teams Table
In this table we keep the teams of the App. Each organization has its own teams, so in order to keep that relationship we use the object_id
which is the id of the specific organization and the role of that team (Reviewer, Author, Editor etc.). Having this, we can assign users to specific roles and associate them to any component of the data model (for example: a Book Component, a Book, a Collection, etc).
Team Member Tables
In this table we keep any relationship between users and the Teams Table. A user can belong to multiple teams and can have a specific status such as: unverified, enabled, disabled.
Comments Tables
This table is related to the user-id
and the Book Component Versions id. Comments can be made on any version of a Book Component.
Not currently shown in the data model
Based in the discussion in #40 (closed) we expect to add a Settings Table.