Database, Table and Column Naming Conventions?

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?

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*.

<pre><code>
--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
);
</code></pre>

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.

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

Take a look at ISO 11179-5: Naming and identification principles
You can get it here: [

[To see links please register here]

][1]

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


[1]:

[To see links please register here]

[2]:

[To see links please register here]

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.

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.

[To see links please register here]

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][1]:

"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.


[1]:

[To see links please register here]

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.

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.