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.
To aid in readability, all database constructs such as tables, columns, nodes, and predicates will have an underscore separating words. For example,
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.
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 table will contain a column named
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.
_uid is used instead of
_uuid. This is because the column isn’t always a UUID type.
The foreign key may or may not have a constraint. Usually in a data warehouse, the database will not contain foreign key constraints even though the database still joins tables together.
UUID and a GUID are essentially the same thing. In the Microsoft world, the term GUID is more common.
_id An integer based foreign key. i.e.
_uid A universal foreign key id. This can be a UUID, integer, character field, etc. i.e.
_xid An external id. This can be any type though it’s often a character type. i.e.
_at A datetime field. i.e.
_on A date field. i.e.
has_ A boolean field. i.e.