SQL Naming Convention - ridz1208/Loris GitHub Wiki

Overview

This document details the preferred naming convention to use for tables, fields and constraints in the Loris SQL database. These guidelines should be followed when submitting a Pull Request to the Loris repository as well as when reviewing the PR of a peer developer.

Disclosure: Due to the previous lack of standard in naming of SQL variables, we acknowledge that existing tables do not abide by the rules described below. As the codebase and database are being cleaned-up, the existing infrastructure will be re-designed to follow this guideline.

Table Naming

Casing: snake_case

Singular: Yes

Conventions:

  • When choosing the name of a table containing data for a single entity in Loris such as a Candidate, Session, User, ...
    • The name of the table should be a noun, as concise as possible and singular. Recommended names for these entities are candidate, session and user.
  • When a single word is not sufficient to describe a table, the ordering of the words in the name should reveal the concern of the table in a broad-to-specific manner.
    • i.e. A table containing the consent information of a candidate would be named candidate_consent, candidate being the broader concern and just consent is not sufficiently informative to rule out user consents and examiner consents . A table containing the consent types would be called as such consent_type.
  • When creating a table representing a relation between two or more tables. Regardless of the relation type (one-to-many, many-to-one or one-to-one), the table should have a composite name as such table1_table2_rel.
    • i.e. the table mapping users to their permissions would be named user_permission_rel where user is the name of the Users' entity table and permission is the name of the Permissions' entity table

Field Naming

Casing: snake_case

Singular: Recommended

Conventions:

  • When naming the primary identifier field of a table, the name should be composed as such table_name_id.
    • i.e. the name of the primary identifier of the candidate table should be candidate_id.
  • When adding a field which serves as a foreign key to another table in the database, the field should follow the same convention as above reference_table_name_id. This allows for simpler joining in SQL queries and for easier readability.
    • i.e. The session table would have a primary identifier field named session_id and a foreign key reference to the candidate table with a field named candidate_id.
  • Avoid ENUM fields.
    • They are evil !!

Constraints (under construction)

Casing: snake_case

Singular: Recommended

Conventions:

  • Be explicit over implicit. ENGINE=InnoDB CHARSET=utf8 ; ON DELETE ... ON UPDATE ... for foreign key constraints.