Database, Table and Column Naming Conventions?

Question

Whenever I design a database, I always wonder if there is a best way of naming an item in my database. Quite often I ask myself the following questions:

1. Should table names be plural?
2. Should column names be singular?
3. Should I prefix tables or columns?
4. Should I use any case in naming items?

Are there any recommended guidelines out there for naming items in a database?

Answer 1

1. No. A table should be named after the entity it represents. Person, not persons is how you would refer to whoever one of the records represents.
2. Again, same thing. The column FirstName really should not be called FirstNames. It all depends on what you want to represent with the column.
3. NO.
4. Yes. Case it for clarity. If you need to have columns like "FirstName", casing will make it easier to read.
   
   Ok. Thats my $0.02

Answer 2

Ok, since we're weighing in with opinion:

I believe that table names should be plural. Tables are a collection (a table) of entities. Each row represents a single entity, and the table represents the collection. So I would call a table of Person entities People (or Persons, whatever takes your fancy).

For those who like to see singular "entity names" in queries, that's what I would use table aliases for:

SELECT person.Name
FROM People person

A bit like LINQ's "from person in people select person.Name".

As for 2, 3 and 4, I agree with @Lars.

Answer 3

I think the best answer to each of those questions would be given by you and your team. It's far more important to have a naming convention then how exactly the naming convention is.

As there's no right answer to that, you should take some time (but not too much) and choose your own conventions and - here's the important part - stick to it.

Of course it's good to seek some information about standards on that, which is what you're asking, but don't get anxious or worried about the number of different answers you might get: choose the one that seems better for you.

Just in case, here are my answers:

1. Yes. A table is a group of records, teachers or actors, so... plural.
2. Yes.
3. I don't use them.
4. The database I use more often - Firebird - keeps everything in upper case, so it doesn't matter. Anyway, when I'm programming I write the names in a way that it's easier to read, like releaseYear.

Answer 4

1. Definitely keep table names singular, person not people
   1. Same here
   2. No. I've seen some terrible prefixes, going so far as to state what were dealing with is a table (tbl) or a user store procedure (usp). This followed by the database name... Don't do it!
   3. Yes. I tend to UpperCamelCase all my table names

Answer 5

My opinions on these are:

1) No, table names should be singular.

While it appears to make sense for the simple selection (select * from Orders) it makes less sense for the OO equivalent (Orders x = new Orders).

A table in a DB is really the set of that entity, it makes more sense once you're using set-logic:

select Orders.*
from Orders inner join Products
    on Orders.Key = Products.Key

That last line, the actual logic of the join, looks confusing with plural table names.

I'm not sure about always using an alias (as Matt suggests) clears that up.

2) They should be singular as they only hold 1 property

3) Never, if the column name is ambiguous (as above where they both have a column called [Key]) the name of the table (or its alias) can distinguish them well enough. You want queries to be quick to type and simple - prefixes add unnecessary complexity.

4) Whatever you want, I'd suggest CapitalCase

I don't think there's one set of absolute guidelines on any of these.

As long as whatever you pick is consistent across the application or DB I don't think it really matters.

Answer 6

--Example SQL

CREATE TABLE D001_Students
(
    StudentID INTEGER CONSTRAINT nnD001_STID NOT NULL,
    ChristianName NVARCHAR(255) CONSTRAINT nnD001_CHNA NOT NULL,
    Surname NVARCHAR(255) CONSTRAINT nnD001_SURN NOT NULL,
    CONSTRAINT pkD001 PRIMARY KEY(StudentID)
);

CREATE INDEX idxD001_STID on D001_Students;

CREATE TABLE D002_Classes
(
    ClassID INTEGER CONSTRAINT nnD002_CLID NOT NULL,
    StudentID INTEGER CONSTRAINT nnD002_STID NOT NULL,
    ClassName NVARCHAR(255) CONSTRAINT nnD002_CLNA NOT NULL,
    CONSTRAINT pkD001 PRIMARY KEY(ClassID, StudentID),
    CONSTRAINT fkD001_STID FOREIGN KEY(StudentID) 
        REFERENCES D001_Students(StudentID)
);

CREATE INDEX idxD002_CLID on D002_Classes;

CREATE VIEW V001_StudentClasses
(
    SELECT
        D001.ChristianName,
        D001.Surname,
        D002.ClassName
    FROM
        D001_Students D001
            INNER JOIN
        D002_Classes D002
            ON
        D001.StudentID = D002.StudentID
);

These are the conventions I was taught, but you should adapt to whatever you developement hose uses.

1. Plural. It is a collection of entities.
2. Yes. The attribute is a representation of singular property of an entity.
3. Yes, prefix table name allows easily trackable naming of all constraints indexes and table aliases.
4. Pascal Case for table and column names, prefix + ALL caps for indexes and constraints.

Answer 7

I work in a database support team with three DBAs and our considered options are:

1. Any naming standard is better than no standard.
2. There is no "one true" standard, we all have our preferences
3. If there is standard already in place, use it. Don't create another standard or muddy the existing standards.

We use singular names for tables. Tables tend to be prefixed with the name of the system (or its acronym). This is useful if the system complex as you can change the prefix to group the tables together logically (ie. reg_customer, reg_booking and regadmin_limits).

For fields we'd expect field names to be include the prefix/acryonm of the table (i.e. cust_address1) and we also prefer the use of a standard set of suffixes ( _id for the PK, _cd for "code", _nm for "name", _nb for "number", _dt for "Date").

The name of the Foriegn key field should be the same as the Primary key field.

i.e.

SELECT cust_nm, cust_add1, booking_dt
FROM reg_customer
INNER JOIN reg_booking
ON reg_customer.cust_id = reg_booking.cust_id

When developing a new project, I'd recommend you write out all the preferred entity names, prefixes and acronyms and give this document to your developers. Then, when they decide to create a new table, they can refer to the document rather than "guess" what the table and fields should be called.

Answer 8

Naming conventions allow the development team to design discovereability and maintainability at the heart of the project.

A good naming convention takes time to evolve but once it’s in place it allows the team to move forward with a common language. A good naming convention grows organically with the project. A good naming convention easily copes with changes during the longest and most important phase of the software lifecycle - service management in production.

Here are my answers:

1. Yes, table names should be plural when they refer to a set of trades, securities, or counterparties for example.
2. Yes.
3. Yes. SQL tables are prefixed with tb, views are prefixed vw, stored procedures are prefixed usp_ and triggers are prefixed tg_ followed by the database name.
4. Column name should be lower case separated by underscore.

Naming is hard but in every organisation there is someone who can name things and in every software team there should be someone who takes responsibility for namings standards and ensures that naming issues like sec_id, sec_value and security_id get resolved early before they get baked into the project.

So what are the basic tenets of a good naming convention and standards: -

• Use the language of your client and your solution domain
• Be descriptive
• Be consistent
• Disambiguate, reflect and refactor
• Don’t use abbreviations unless they are clear to everyone
• Don’t use SQL reserved keywords as column names

Answer 9

I recommend checking out Microsoft's SQL Server sample databases: http://codeplex.com/SqlServerSamples

The AdventureWorks sample uses a very clear and consistent naming convention that uses schema names for the organization of database objects.

1. Singular names for tables
2. Singular names for columns
3. Schema name for tables prefix (E.g.: SchemeName.TableName)
4. Pascal casing

Answer 10

Take a look at ISO 11179-5: Naming and identification principles You can get it here: http://metadata-standards.org/11179/#11179-5

I blogged about it a while back here: ISO-11179 Naming Conventions

Answer 11

In my opinion:

1. Table names should be plural.
2. Column names should be singular.
3. No.
4. Either CamelCase (my preferred) or underscore_separated for both table names and column names.

However, like it has been mentioned, any convention is better than no convention. No matter how you choose to do it, document it so that future modifications follow the same conventions.

Answer 12

Here's a link that offers a few choices. I was searching for a simple spec I could follow rather than having to rely on a partially defined one.

http://justinsomnia.org/writings/naming_conventions.html

Answer 13

I'm also in favour of a ISO/IEC 11179 style naming convention, noting they are guidelines rather than being prescriptive.

See Data element name on Wikipedia:

"Tables are Collections of Entities, and follow Collection naming guidelines. Ideally, a collective name is used: eg., Personnel. Plural is also correct: Employees. Incorrect names include: Employee, tblEmployee, and EmployeeTable."

As always, there are exceptions to rules e.g. a table which always has exactly one row may be better with a singular name e.g. a config table. And consistency is of utmost importance: check whether you shop has a convention and, if so, follow it; if you don't like it then do a business case to have it changed rather than being the lone ranger.

Answer 14

Late answer here, but in short:

1. My preference is plural
2. Yes
3. Tables: *Usually* no prefixes is best. Columns: No.
4. Both tables and columns: Pascal casing.

Elaboration:

(1) What you must do. There are very few things that you must do a certain way, every time, but there are a few.

• Name your primary keys using "[singularOfTableName]ID" format. That is, whether your table name is Customer or Customers, the primary key should be CustomerID.
• Further, foreign keys must be named consistently in different tables. It should be legal to beat up someone who does not do this. I would submit that while defined foreign key constraints are often important, consistent foreign key naming is always important
• You database must have internal conventions. Even though in later sections you'll see me being very flexible, within a database naming must be very consistent . Whether your table for customers is called Customers or Customer is less important than that you do it the same way throughout the same database. And you can flip a coin to determine how to use underscores, but then you must keep using them the same way. If you don't do this, you are a bad person who should have low self-esteem.

(2) What you should probably do.

• Fields representing the same kind of data on different tables should be named the same. Don't have Zip on one table and ZipCode on another.
• To separate words in your table or column names, use PascalCasing. Using camelCasing would not be intrinsically problematic, but that's not the convention and it would look funny. I'll address underscores in a moment. (You may not use ALLCAPS as in the olden days. OBNOXIOUSTABLE.ANNOYING_COLUMN was okay in DB2 20 years ago, but not now.)
• Don't artifically shorten or abbreviate words. It is better for a name to be long and clear than short and confusing. Ultra-short names is a holdover from darker, more savage times. Cus_AddRef. What on earth is that? Custodial Addressee Reference? Customer Additional Refund? Custom Address Referral?

(3) What you should consider.

• I really think you should have plural names for tables; some think singular. Read the arguments elsewhere. Column names should be singular however. Even if you use plural table names, tables that represent combinations of other tables might be in the singular. For example, if you have a Promotions and an Items table, a table representing an item being a part of a promotion could be Promotions_Items, but it could also legitimately be Promotion_Items I think (reflecting the one-to-many relationship).
• Use underscores consistently and for a particular purpose. Just general tables names should be clear enough with PascalCasing; you don't need underscores to separate words. Save underscores either (a) to indicate an associative table or (b) for prefixing, which I'll address in the next bullet.
• Prefixing is neither good or bad. It usually is not best. In your first db or two, I would not suggest using prefixes for general thematic grouping of tables. Tables end up not fitting your categories easily, and it can actually make it harder to find tables. With experience, you can plan and apply a prefixing scheme that does more good than harm. I worked in a db once where data tables began with tbl, config tables with ctbl, views with vew, proc's sp, and udf's fn, and a few others; it was meticulously, consistently applied so it worked out okay. The only time you NEED prefixes is when you have really separate solutions that for some reason reside in the same db; prefixing them can be very helpful in grouping the tables. Prefixing is also okay for special situations, like for temporary tables that you want to stand out.
• Very seldom (if ever) would you want to prefix columns.

Answer 15

SELECT 
   UserID, FirstName, MiddleInitial, Lastname
FROM Users
ORDER BY Lastname

Answer 16

our preference:

1. Should table names be plural? Never. The arguements for it being a collection make sense, but you never know what the table is going to contain (0,1 or many items). Plural rules make the naming unnecessarily complicated. 1 House, 2 houses, mouse vs mice, person vs people, and we haven't even looked at any other languages.

Update person set property = 'value' acts on each person in the table. Select * from person where person.name = 'Greg' returns a collection/rowset of person rows.

1. Should column names be singular? Usually, yes, except where you are breaking normalisation rules.

2. Should I prefix tables or columns? Mostly a platform preference. We prefer to prefix columns with the table name. We don't prefix tables, but we do prefix views (v_) and stored_procedures (sp_ or f_ (function)). That helps people who want to try to upday v_person.age which is actually a calculated field in a view (which can't be UPDATEd anyway).

It is also a great way to avoid keyword collision (delivery.from breaks, but delivery_from does not).

It does make the code more verbose, but often aids in readability.

bob = new person() bob.person_name = 'Bob' bob.person_dob = '1958-12-21'

... is very readable and explicit. This can get out of hand though:

customer.customer_customer_type_id

indicates a relationship between customer and the customer_type table, indicates the primary key on the customer_type table (customer_type_id) and if you ever see 'customer_customer_type_id' whilst debugging a query, you know instantly where it is from (customer table).

or where you have a M-M relationship between customer_type and customer_category (only certain types are available to certain categories)

customer_category_customer_type_id

... is a little (!) on the long side.

1. Should I use any case in naming items? Yes - lower case :), with underscores. These are very readable and cross platform. Together with 3 above it also makes sense.

Most of these are preferences though. - As long as you are consistent, it should be predictable for anyone that has to read it.