Naming Conventions: Singular or Plural, snake_case or PascalCase

Every part so far has written patients, patient_id and fk_appointments_patients without saying why. This part says why, and shows the alternative going wrong. The same table is created as "Patients" and as patients, PostgreSQL folds one and not the other, SQL Server's collation decides what matches, two tables end up differing only by case, and a query silently reads the wrong one. Then the rule list, and what EF Core does about it.

Two engines, two ideas of "the same name"

Section 1 creates the table the way EF Core does by default when no naming convention is configured: PascalCase, quoted:

CREATE TABLE "Patients" (
    "Id"        bigint GENERATED BY DEFAULT AS IDENTITY,
    "FullName"  character varying(200) NOT NULL,
    "Phone"     character varying(30)  NOT NULL,
    CONSTRAINT "PK_Patients" PRIMARY KEY ("Id")
);
CREATE UNIQUE INDEX "IX_Patients_Phone" ON "Patients" ("Phone");

Then section 2 queries it the way a person types:

SELECT * FROM Patients;
ERROR:  42P01: relation "patients" does not exist

PostgreSQL folds every unquoted identifier to lower case, so Patients means patients, which was never created. Quoting the table is not enough either: SELECT FullName FROM "Patients" fails with column "fullname" does not exist and a hint, Perhaps you meant to reference the column "Patients.FullName". Once a name is quoted at creation, every query must quote it for the rest of its life. Folding happens at creation too: CREATE TABLE Doctors (Id bigint, FullName text) is stored as doctors(id, fullname), and SELECT * FROM DOCTORS works, because unquoted names are all the same name.

SQL Server keeps the case you wrote and matches through the database collation. Under the default, SQL_Latin1_General_CP1_CI_AS, SELECT * FROM PATIENTS and SELECT fullname, PHONE FROM patients both find [Patients], and so does WHERE FullName = N'maria garcia', because data comparisons follow the same collation. In a database created COLLATE Latin1_General_100_CS_AS the same queries fail with Invalid object name 'PATIENTS' and Invalid column name 'fullname'. The script creates such a database and shows both.

The trap: two tables that differ by case

Section 3 creates patients, lower-case and unquoted, next to "Patients". PostgreSQL allows it, SQL Server allows it only in a case-sensitive database. Now the unquoted query has a table to find:

SELECT count(*) AS rows_in_Patients_unquoted FROM Patients;
 rows_in_patients_unquoted
---------------------------
                         0

SELECT count(*) AS rows_in_quoted_Patients FROM "Patients";
 rows_in_quoted_patients
-------------------------
                       1

No error, a wrong answer, and even the column alias came back folded. This is the strongest argument for one spelling everywhere: lower case, underscores, never quoted, so that there is nothing to fold and nothing to collate.

Names you chose, names the engine chose

Section 4 creates the appointments table twice, once with every constraint named as this series does, once with none named, and makes the same mistake in both: an appointment for patient 99. The named table answers violates foreign key constraint "fk_appointments_patients". The unnamed one answers with what the engine generated:

 appointments         | c       | ck_appointments_status
 appointments         | f       | fk_appointments_patients
 appointments         | p       | pk_appointments
 appointments         | u       | uq_appointments_confirmation_code
 appointments_unnamed | c       | appointments_unnamed_status_check
 appointments_unnamed | f       | appointments_unnamed_patient_id_fkey
 appointments_unnamed | p       | appointments_unnamed_pkey
 appointments_unnamed | u       | appointments_unnamed_confirmation_code_key

PostgreSQL's generated names are readable and predictable. SQL Server's are not: the same mistake there is refused by FK__appointme__patie__45F365D3, and its unnamed siblings are PK__appointm__3213E83F77FA2E25, UQ__appointm__57273C30B4BDD989, CK__appointme__statu__47DBAE45 and DF__appointme__statu__46E78A0C, with a hex suffix that is different in every database the script runs in, which means a deployment script that drops a constraint by name cannot be written. SQL Server also refuses CREATE INDEX ON appointments_unnamed (starts_at) outright, Incorrect syntax near the keyword 'ON'; PostgreSQL accepts it and calls the index appointments_unnamed_starts_at_idx.

Section 5 puts a limit on the naming enthusiasm. An index called ix_appointments_patient_id_starts_at_status_for_the_patient_history_screen is 74 characters. SQL Server stores it whole; PostgreSQL prints NOTICE: identifier ... will be truncated to "ix_appointments_patient_id_starts_at_status_for_the_patient_his" and stores 63, which is its limit for every identifier. Keep names under 63 and both engines keep them.

Reserved words

A spreadsheet can have a column called user. Section 6 tries the obvious table names: CREATE TABLE user and CREATE TABLE order are syntax errors on both engines, and key is refused as a column name by SQL Server (Incorrect syntax near the keyword 'key') while PostgreSQL accepts settings (key text, value text). Quotes make any word legal, and then every query must remember them, or get this:

SELECT user AS what_you_get, "user" AS what_you_meant FROM "order";
 what_you_get | what_you_meant
--------------+------------------
 dd           | reception-desk-2

Unquoted, user is a function that returns the login, dd here and dbo on SQL Server. The fix is not quoting; it is a name that is not a keyword: orders, created_by, setting_key.

The rules

1. Tables: plural, lower-case, snake_case, never quoted: patients, queue_entries.
2. The primary key column is id; a foreign key column is the singular entity plus _id: patient_id.
3. Timestamps are created_at and updated_at, in UTC.
4. Every constraint and index is named by kind: pk_<table>, fk_<table>_<referenced table>, uq_<table>_<column>, ck_<table>_<rule>, ix_<table>_<columns>, and on SQL Server df_<table>_<column> for defaults.
5. No reserved words: orders not order, created_by not user, setting_key not key.
6. Every name within 63 characters.
7. In EF Core, get all of this from UseSnakeCaseNamingConvention() in the EFCore.NamingConventions package rather than from attributes on every property.

Frequently asked

Should database table names be singular or plural?

Pick one and keep it; this series uses plural snake_case (patients, appointments) because a table holds many rows and the convention matches what EF Core's snake-case package produces from DbSet names. The foreign key column stays singular: patient_id.

Why does PostgreSQL say relation does not exist when the table is there?

Unquoted identifiers are folded to lower case, so Patients means patients. If the table was created with quotes as "Patients", only the quoted spelling finds it. Create tables in lower case without quotes and the problem never appears.

Is SQL Server case-sensitive for table and column names?

It depends on the database collation. Under the default case-insensitive collation any spelling matches, and data comparisons ignore case too. Under a case-sensitive collation the exact spelling is required, and two tables differing only by case can coexist.

Next: Part 9, indexes, where 200,000 appointments make the difference between a scan and a seek visible in the plan and the clock.