Naming Conventions

Over the past 20 years, I’ve worked in systems that don’t follow a naming convention and those that do. Those that have a convention are often the result of an ORM or a strong engineering community. When things were named consistently, it aided in the discovery of relationships, data types, and readability.

This also helps when doing database audits to see if there are columns that should be indexed, but aren’t. For example, if a column ends in _id, it is a relationship to another table and will most likely need to be indexed.

I’ll use the following conventions throughout the book.

Underscore separate words

To aid in readability, all database constructs such as tables, columns, nodes, and predicates will have an underscore separating words. For example, customer_addresses, user_id, or first_name.

Singular vs. Plural names

Tables are assumed to contain many tuples, and node types are assumed to have many node instances. Because of this, they’ll all use the plural names.

Specific columns or predicates, however, represent the datum for one particular tuple. Therefore, these will always be singular.

For example, a table might be named customers, with the columns first_name, last_name, is_active, inserted_at, updated_at.

Relationships

An integer primary key will always be labeled id, while an integer foreign key will use the singular version of the table followed by _id. For example, if a table named users has a one-to-many relationship with a table called addresses, the addresses table will contain a column named user_id.

If the relationship is using a universal id, such as with the data type UUID, the prefix is _uid. These can be integers, UUIDs, character fields, etc. It just has to be universally unique.

Notes:

Column Prefix and Suffix Conventions