CRMS SQL Naming Convention

This is a guide on the naming convention for Competition Results Management System (CRMS)

Overall Case formatting:

Fields	Case
Database names	Lower case
Table names	Pascal case + snake case
Column names	Lower case + snake case
Keys and Indexes	Prefix = uppercase

Definition of Case formatting:

Refer to the picture below to understand the different case formatting.

---

Database

Database names will be lowercase (no numbers or special characters like "-/+=", only "_" underscores are allowed).

---

Tables

Table names will be in lowercase and singular. No prefix is required. E.g. athlete, team, sport, etc. If table name has multiple words, concatenate them with an underscore "_". E.g. "Competition Geo Location" --> competition_geo_location.

Naming convention will follow "Parent-Child" relationship. E.g. Athlete results will be named as athlete_result instead of result_athlete

Views

View names will be in lowercase, singular and be prefixed with "vw_". E.g. vw_country.

Many to Many Tables

For many to many tables, table names will be concatenated with 2 underscores '__'. E.g. competition__competition_category.

---

Columns

Column names will be singular and lowercase except for prefixes and suffixes. All prefixes and suffixes should be in lowercase. Column names with more than one word will be joined using underscores '_'

Primary Key Column

Primary Keys will be prefixed with 'pk_'

Foreign Key Column

Foreign Keys will be prefixed with 'fk_'

---

Constraints Naming

Constraints naming will be in the following format: {tablename}{columnname(s)}{suffix} (for primary key just {tablename}_{suffix} will suffice)

Primary Keys

Primary Keys will be suffixed with '_pkey'

Foreign Keys

Foreign Keys will be suffixed with '_fkey'

Indexes

Indexes will be suffixed with '_idx'

Unique Key Constraints

Unique Key constraints will be suffixed with '_key'

Exclusion Constraints

Exclusion constraints will be suffixed with '_excl'

Check Constraints

Exclusion constraints will be suffixed with '_check'

---

Sequence

Sequence will be suffixed with '_seq'

---

Data Types Format

Date

Date fields will be in the format "YYYY-MM-DD"

DateTime

Date fields will be in the format "YYYY-MM-DD HH:MM:SS"

Time

Time fields will be in the format "HH:MM:SS"