Common Conventions

Concept conventions

• use record to mean a digital resource stored in table records created by a user in ADA. Equivalent to resource in datacite schema and data product in SAMIS data product Documents.

• use pair [row, column] instead of [record, field] to represent table composition.

Naming conventions

• identifiers (names of tables, columns, etc):

  • use full language words for all object names, e.g. use number instead of num.

  • comply with snake case, e.g. record_creators, records.

• table name: should be named using a plural word, e.g. records.

• id column (primary key): use id.

• id column (foreign key): use the table name (singular) followd by “id” (e.g. record_id).

• value list: comply with upper camel case, e.g. PhysicalObject, Service.

Character type

Use the text data type for columns that store strings.

SERIAL type id column

Each table has a SERIAL type id column as primary key, which stores a sequential integer, of the INT data type, that is automatically assigned by the database server when a new row is inserted.

Note

Don’t need to populate the id column when create new row in the table. It is automatically generated by database.

Two automatic timestamps

There are two timestamp columns created_at and updated_at present in most tables. Both of them are set to NOW() defaultly in each table. Each table also has a trigger set_timestamp that will execute the trigger_set_timestamp function , which will do so whenever a row is updated in the table. Both the created_at and updated_at columns will be saved correctly whenever insert and update rows in the table.

Note

Don’t need to populate either the created_at or updated_at columns when insert and update rows in the table. They are automatically generated by database.

Value List

In ADA, value list is used when a column’s values are restricted to valid picklists, which are usually self described without additinal information, e.g. Genders: male, female , …

• Record General Type Value List

  • Column general_type (M) in table records

• DOI Status Value List

  • Column doi_status (MA) in table records

• Submission Type Value List

  • Column submission_type (M) in table records

• Process Status Value List

  • Column process_status (M) in table records

• Contributor Type Value List

  • Column contributor_type (M) in table record_contributors

• Name Type Value List

  • Column name_type (M) in table name_entities

• Record File General Type Value List

  • Column general_type (MA) in table record_files

Implementation

We apply CHECK constraints on those columns that expect a list of values.

Example

CONSTRAINT name_entities_name_type_check CHECK (((name_type = ANY (ARRAY['Personal'::text, 'Organizational'::text]))))

Lookup Table

This approach store the controlled lists in a table instead of being part of the schema. Foreign keys are used to ensure the validity of the values in the target tables.

• Usage

  • Table external_identifier_schemes used by table name_entity_identifiers

  • Table subject_schemes used by table record_subjects

  • Table file_extensions used by table record_files

  • Table funders used by table record_fundings

  • Table licenses used by table record_licenses