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, customer_addresses, user_id, or first_name.
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, 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 suffix is _uid. These can be integers, UUIDs, character fields, etc. It just has to be universally unique.
Notes:
_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. user_id
_uid A universal foreign key id. This can be a UUID, integer, character field, etc. i.e. address_uid
_xid An external id. This can be any type though it’s often a character type. i.e. google_place_xid
_at A datetime field. i.e. inserted_at
_on A date field. i.e. published_on
is_ or has_ A boolean field. i.e. is_active or has_logged_in