Database, Table and Column Naming Conventions?

[Essential Database Naming Conventions (and Style)][1] (click here for more detailed description)

table names
choose short, unambiguous names, using no more than one or two words
distinguish tables easily
facilitates the naming of unique field names as well as lookup and linking tables
give tables singular names, never plural (update: i still agree with the reasons given for this convention, but most people really like plural table names, so i’ve softened my stance)... follow the link above please

[1]:

[To see links please register here]

1. Definitely keep table names singular, person not people
2. Same here
3. 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!
4. Yes. I tend to PascalCase all my table names

Table names singular. Let's say you were modelling a realtionship between someone and their address.
For example, if you are reading a datamodel would you prefer
'each person may live at 0,1 or many address.' or
'each people may live at 0,1 or many addresses.'
I think its easier to pluralise address, rather than have to rephrase people as person. Plus collective nouns are quite often dissimlar to the singular version.

I know this is late to the game, and the question has been answered very well already, but I want to offer my opinion on #3 regarding the prefixing of column names.

**All columns should be named with a prefix that is unique to the table they are defined in.**

E.g. Given tables "customer" and "address", let's go with prefixes of "cust" and "addr", respectively. "customer" would have "cust_id", "cust_name", etc. in it. "address" would have "addr_id", "addr_cust_id" (FK back to customer), "addr_street", etc. in it.

When I was first presented with this standard, I was dead-set against it; I hated the idea. I couldn't stand the idea of all that extra typing and redundancy. Now I've had enough experience with it that I'd never go back.

The result of doing this is that all of the columns in your database schema are unique. There is one major benefit to this, which trumps all arguments against it (in my opinion, of course):

**You can search your entire code base and reliably find every line of code that touches a particular column.**

The benefit from #1 is incredibly huge. I can deprecate a column and know exactly what files need to be updated before the column can safely be removed from the schema. I can change the meaning of a column and know exactly what code needs to be refactored. Or I can simply tell if data from a column is even being used in a particular portion of the system. I can't count the number of times this has turned a potentially huge project into a simple one, nor the amount of hours we've saved in development work.

Another, relatively minor benefit to it is that you only have to use table-aliases when you do a self join:

SELECT cust_id, cust_name, addr_street, addr_city, addr_state
FROM customer
INNER JOIN address ON addr_cust_id = cust_id
WHERE cust_name LIKE 'J%';

**Table Name:** It should be singular, as it is a singular entity representing a real world object and not objects, which is singlular.

**Column Name:** It should be singular only then it conveys that it will hold an atomic value and will confirm to the normalization theory. If however, there are n number of same type of properties, then they should be suffixed with 1, 2, ..., n, etc.

Prefixing Tables / Columns: It is a huge topic, will discuss later.

Casing: It should be Camel case

My friend, **Patrick Karcher**, I request you to please not write anything which may be offensive to somebody, as you wrote, "•Further, foreign keys must be named consistently in different tables. It should be legal to beat up someone who does not do this.". I have never done this mistake my friend Patrick, but I am writing generally. What if they together plan to beat you for this? :)

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.

Table names should always be singular, because they represent a set of objects. As you say herd to designate a group of sheep, or flock do designate a group of birds. No need for plural. When a table name is composition of two names and naming convention is in plural it becomes hard to know if the plural name should be the first word or second word or both.
It’s the logic – Object.instance, not objects.instance. Or TableName.column, not TableNames.column(s).
Microsoft SQL is not case sensitive, it’s easier to read table names, if upper case letters are used, to separate table or column names when they are composed of two or more names.

Very late to the party but I still wanted to add my two cents about column prefixes

There seem to be two main arguments for using the table_column (or tableColumn) naming standard for columns, both based on the fact that the column name itself will be unique across your whole database:

1) You do not have to specify table names and/or column aliases in your queries all the time

2) You can easily search your whole code for the column name

I think both arguments are flawed. The solution for both problems without using prefixes is easy. Here's my proposal:

**Always use the table name in your SQL. E.g., always use table.column instead of column.**

It obviously solves 2) as you can now just search for table.column instead of table_column.

But I can hear you scream, how does it solve 1)? It was exactly about avoiding this. Yes, it was, but the solution was horribly flawed. Why? Well, the prefix solution boils down to:
To avoid having to specify table.column when there's ambiguity, you name all your columns table_column!
But this means you will from now on ALWAYS have to write the column name every time you specify a column. But if you have to do that anyways, what's the benefit over always explicitly writing table.column? Exactly, there is no benefit, it's the exact same number of characters to type.

edit: yes, I am aware that naming the columns with the prefix enforces the correct usage whereas my approach relies on the programmers

our preference:

1. Should table names be plural?
Never. The arguments 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.

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

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

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