Data model: Difference between revisions

(Created page with "The Miranda Digital Assset Management Platform is designed to support an arbitrary number of different content types, each defined via a JSON schema. Each of these content ty...")
 
(adding db schema stuff)
Line 1: Line 1:
The Miranda Digital Assset Management Platform is designed to support an arbitrary number of different content types, each defined via a JSON schema.  Each of these content type objects is stored within a NoSQL field within a postgresql table that contains a variety of other fields that help manage the data access rules around that content and provide metadata about when that content was last imported into the system from an external source or edited within the DAP.
The Miranda Digital Assset Management Platform is designed to support an arbitrary number of different content types, each defined via a JSON schema.  Each of these content type objects is stored within a NoSQL field within a postgresql table that contains a variety of other fields that help manage the data access rules around that content and provide metadata about when that content was last imported into the system from an external source or edited within the DAP.
=== Database Schema ===
==== Content Table ====
{| class="confluenceTable"
!Name
!Type
!Description
|-
|id
|integer
|Serial identifier in the database for relational indexes. Not for use in API.
|-
|id_dap
|uuid <guid>
|Universal identifier for the DAP record across services. Used to request records by ID in API.
|-
|date_created
|datetime
|On first insertion. Do not change this on updates.
|-
|date_updated
|datetime
|On first insertion set to the same exact value as date_created.
|-
|type
|string
|The record type allows well-indexed querying to differentiate by some top-level categorization of records. It is also a lookup identifier for record-specific configuration or logic.
Types include: content (a content item), collection (a set of content items, grouped together to demonstrate a related collection for users), container (a means of organizing a sub-set of related items as part of a larger content record.
|-
|metadata
|jsonb
|The full schema-compliant metadata record. It should include everything needed for surfacing this discrete item in the API or pushing to the search index. (Excluding questions of related records.)
|}
==== Import Management Table ====
{| class="confluenceTable"
!Field Name
!Field Type
!Description
|-
|id
|integer
|Foreign key if this is a separate table.
|-
|id_dap
|uuid <guid>
|Universal identifier for the DAP record across services. Used to request records by ID in API.
|-
|remote_system
|string
|Machine name in the DAP system recognizing the system of record. This is used to lookup details on the originating system which might be used for configuring update rules, specifying an import or systems connection process or what not.
|-
|remote_id
|string
|If the remote system utilizes a GUID or ID system, that ID should be captured here
|-
|date_last_import
|datetime
|This is only incremented if the status of the import is successful. If an item is only ever updated via importer, this will always be the same as the date_changed core column. If we allow records to be overridden or switch to manual management, this becomes a marker for divergence.
|-
|date_last_import_attempt
|datetime
|This is updated any time import of a specific item is attempted.
|-
|status_import
|integer <smallint>
|Status code associated with the last import. This is used for report building, while general system logging and monitoring are used for audit.
|}

Revision as of 19:09, 20 October 2017

The Miranda Digital Assset Management Platform is designed to support an arbitrary number of different content types, each defined via a JSON schema. Each of these content type objects is stored within a NoSQL field within a postgresql table that contains a variety of other fields that help manage the data access rules around that content and provide metadata about when that content was last imported into the system from an external source or edited within the DAP.

Database Schema

Content Table

Name Type Description
id integer Serial identifier in the database for relational indexes. Not for use in API.
id_dap uuid <guid> Universal identifier for the DAP record across services. Used to request records by ID in API.
date_created datetime On first insertion. Do not change this on updates.
date_updated datetime On first insertion set to the same exact value as date_created.
type string The record type allows well-indexed querying to differentiate by some top-level categorization of records. It is also a lookup identifier for record-specific configuration or logic.

Types include: content (a content item), collection (a set of content items, grouped together to demonstrate a related collection for users), container (a means of organizing a sub-set of related items as part of a larger content record.

metadata jsonb The full schema-compliant metadata record. It should include everything needed for surfacing this discrete item in the API or pushing to the search index. (Excluding questions of related records.)

Import Management Table

Field Name Field Type Description
id integer Foreign key if this is a separate table.
id_dap uuid <guid> Universal identifier for the DAP record across services. Used to request records by ID in API.
remote_system string Machine name in the DAP system recognizing the system of record. This is used to lookup details on the originating system which might be used for configuring update rules, specifying an import or systems connection process or what not.
remote_id string If the remote system utilizes a GUID or ID system, that ID should be captured here
date_last_import datetime This is only incremented if the status of the import is successful. If an item is only ever updated via importer, this will always be the same as the date_changed core column. If we allow records to be overridden or switch to manual management, this becomes a marker for divergence.
date_last_import_attempt datetime This is updated any time import of a specific item is attempted.
status_import integer <smallint> Status code associated with the last import. This is used for report building, while general system logging and monitoring are used for audit.