Pragmatic Database Schema Naming Conventions, Practices, and Patterns

What We’re Working With, Some of The Database Rules

Table, Column, Tuple, or Related Naming Conventions

  • Table, column, and related object names should contain only letters, numbers as characters in the body of the name and not as the preface characters, underscores and absolutely no spaces or special characters. In summary, use a case scheme like Camel or Pascal Case but do not use Snake or Kebab Case.
  • Use meaningful names from the business or organizational domain being modeled. Such as “BankingUsers”, “Transactions”, “railroads”, or “railroad_Systems”.
  • Use singular word names if at all possible, only moving to compound word naming if absolutely necessary. Ideally names would be single words like “User”, “Transactions”, “railroad”, or “system” and exclude compound names like “railroadSystem” until it is needed to prevent confusion or naming collisions.
  • Columns that are primary or foreign keys should be prefaced with PK_ and FK_ respectively, and in my moderately humble opinion, stick to just PK or FK using Camel or Pascal Case. For other metadata, indexes, and related names use a respective preface or postfix conventions.
  • It’s also a good idea to choose plural or singular for table names. However, be sure to choose one or the other so that frameworks, Object Relationship/Relational Mappers/Mapping (ORM), and other tools can effectively name things when used. For example, when table names are singular, many ORM frameworks when generating code would take a singular table name like Customer and make it Customers and have objects of Customer.

Schema/Domain Naming Conventions

  • Purchase
  • Return
  • Shipped
  • Price
  • Cost
  • Item
  • Core
  • Prices
  • Returns

An Example

Database Schema
CREATE TABLE "Source" (
"Id" uuid PRIMARY KEY,
"Stamp" timestamp,
"Name" text,
"Uri" text,
"Details" text
);
CREATE TABLE "SourceNotes" (
"SourceId" uuid,
"NotesId" uuid,
"Details" text,
"Stamp" timestamp
);
CREATE TABLE "NoteJot" (
"Id" uuid PRIMARY KEY,
"Stamp" timestamp,
"NoteId" uuid,
"Details" text
);
CREATE TABLE "Action" (
"Id" uuid PRIMARY KEY,
"Stamp" timestamp,
"Action" json
);
CREATE TABLE "Connection" (
"Id" uuid PRIMARY KEY,
"Stamp" timestamp,
"ActionId" uuid,
"SourceId" uuid
);
CREATE TABLE "Formatter" (
"Id" uuid PRIMARY KEY,
"Stamp" timestamp,
"ConnectionId" uuid,
"FormatterMap" json
);
CREATE TABLE "Schema" (
"Id" uuid PRIMARY KEY,
"Stamp" timestamp,
"ConnectionId" uuid,
"SchemaMap" json
);
ALTER TABLE "SourceNotes" ADD FOREIGN KEY ("SourceId") REFERENCES "Source" ("Id");ALTER TABLE "SourceNotes" ADD FOREIGN KEY ("NotesId") REFERENCES "NoteJot" ("Id");ALTER TABLE "NoteJot" ADD FOREIGN KEY ("NoteId") REFERENCES "NoteJot" ("Id");ALTER TABLE "Connection" ADD FOREIGN KEY ("ActionId") REFERENCES "Action" ("Id");ALTER TABLE "Connection" ADD FOREIGN KEY ("SourceId") REFERENCES "Source" ("Id");ALTER TABLE "Formatter" ADD FOREIGN KEY ("ConnectionId") REFERENCES "Connection" ("Id");ALTER TABLE "Schema" ADD FOREIGN KEY ("ConnectionId") REFERENCES "Connection" ("Id");

Do NOT Use These

  • Generally throughout databases it is best to skip Snake or Kebab Case. Various situations they’re fine, but overall they’re likely to run into conflicts, naming limitations, or other concerns. It’s best to just skip them and remove the concern.
  • When you’re using data that has variance in how it is represented, do not use multitudes of formats. For dates, location, geographic, or related data it is best to stick to a particular format that is repeated throughout the database. Using mixed representations, for example with dates a MMDDYYYY format and then a DD-MM-YYYY format, methods, functions, or other elements that consume or process this data will need to account for this. Creating more time consuming and error prone code.
  • Once you pick a naming scheme for any particular database object type, stick to the naming scheme. For example, if you go with Camel Casing for your tables, use Camel Casing for all of the tables and don’t switch to Pascal for some of them. Specifically, however with this guidance, is if you switch object types, for example you name the tables Pascal Cased but switch to Camel Case for indexes, that’s perfect. Then if ever reviewing a list of objects irrespective of kinds of objects, one can differentiate merely by the conventions used.

Summary & Caveat

--

--

--

Software dev, data, heavy metal, transit, economics, freethought, atheism, cycling, livability, beautiful things & adrenaline junkie.

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

Building a Chip-8 Emulator in Go

How to Add and Remove Laravel Events | Yudiz Solutions

How to access and get the Youtube data through API in Flutter

Ace AWS Advanced Networking Specialty Exam ANS-C00

Meet the Adobe I/O Team: Mihai Corlan on Building Adobe’s Serverless Platform

In the CRM world, the following joke is becoming increasingly popular: “If you are currently a…

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Adron Hall

Adron Hall

Software dev, data, heavy metal, transit, economics, freethought, atheism, cycling, livability, beautiful things & adrenaline junkie.

More from Medium

Designing nRT Read and write optimized CDC Framework for Hadoop Data Lake — Part 2

Query prioritization in Apache Druid

In this blog, I am going to explain you how State store in Kafka streams is managed in…

Decouple with Trust — Event-Driven Architecture (EDA) is inevitable for Banking Platform as a…