Database naming conventions

Question

I'm starting a new project and want to begin with a database naming convention that won't become a hindrance in the future.

I want a consistent convention for tables and columns. At a previous employer I have seen all objects in upper case with underscores separating words.

e.g.

CREATE TABLE USER_TYPES (USER_TYPE_ID INT);

Is this the best way to go?

I've also seen the use of pascal notation in a few places, e.g.

CREATE TABLE UserTypes (UserTypeId INT);

This certainly looks nicer but are there any potential pitfalls?

I'm using PostgreSQL but I think this applies to any database system. I'd like to get a consensus from the members of this site as I haven't seen a defined standard.

Answer 1

Just choose the one that you like and stick to it.

Answer 2

I generally try to avoid the plurals in table names simply because it's universally implied. Other than that, I'm not aware of any pitfalls for choosing one convention over the other that doesn't involve a holy war.

Pick the convention that is most reasonable and intuitive to you and stick with it. Consistency is king. :)

Answer 3

Well, you might have some issues by using uppercase and lowercase letters in your naming convetion:

Imagine this:

CREATE OR REPLACE TABLE Foo( ID_Foo serial not null, Foo_Type integer not null, CONSTRAINT Foo_PK PRIMARY KEY(ID_Foo));

This will produce a table all with lowercase names. If you really really want to use uppercase, you will need to do this:

CREATE OR REPLACE TABLE "Foo"( "ID_Foo" serial not null, "Foo_Type" integer not null, CONSTRAINT "Foo_PK" PRIMARY KEY("ID_Foo"));

That can become quite annoying if you have a big system, because you need to use the same casing on queries as well, and always use delimiters.

SELECT "ID_Foo" FROM "Foo";

Your best option is to keep it simple: all lowercase and the rest is up to you. Many people like to use prefixes like tbl for table, idx for indexes, pk for primary keys (pks names), fk for foreign keys, and so this goes.

Just be consistent with the convention you chosen.

Edit as far the plural vs. singular goes i like to keep table names in singular form. I like the table name to describe a singular object it is holding, not its collection. e.g. car, instead of cars. driver instead of drivers.

Answer 4

We went through the process of creating database standards in our organization and I was tasked with leading that effort. I found that consistency often yields greater benefits than specific standards.

Ultimately we went with Pascal notation, plural table names (not my preference), and a plethora of other naming-related standards for tables, columns, indexes, views, procedures, functions, etc..

In your standards document, be sure to flag the "holy war" conventions with a footnote indicating that you realize the convention isn't universally accepted but that a decision was made for consistency. This helps dissenters accept conventions that they disagree with.

Answer 5

I tend to use somewhat verbose names to make things easier to understand. I don't go overboard but I would use "EmployeeID" as a column of the Employee table instead of just "ID".

To me it just makes things easier when you use columns as foreign keys in other tables. This way there is no ambiguity as to which ID a foreign key column is pointing at. Sure it requires more typing but I'd rather type more than risk confusion for future development.

Answer 6

The rules:

1. No matter what you will ultimately decide, be consistent.
2. Use a common naming convention for specifying special columns like primary and foreign keys (like adding a "ID" or "FK" suffix or whatever suits you).
3. Use a common convention for all acronyms (all UPPERCASE, or camelCase) like URL / Url. Keep the acronyms consistent.
4. Make sure you always use the same word to describe the same thing (don't use "Lastname" in one table and "name" in other, or "sex" and "gender").

Your options:

Casing: All lowercase, all UPPERCASE, all camelCase, all_underscores

Singular or plural: User or Users?

Prefix or suffix (or none): UserID, IDUser, ID as primary key or UserFK, FKUser, User as foreign key

Bonus:

Also consider making a standard convention for naming other DB objects like constraints. Naming constraints explicitly instead of using names autogenerated by DB can help in writing maintenance / migration scripts (for example name your default constraints like DefTableColumn - DefUserActive or the DB will create constrains named like Def_2435jhk234ghj234).