DevCampus Database Naming Conventions

re: DevCampus Database Naming Conventions

cng — Wed, 04 Nov 2009 22:41:28 GMT

I ran across your blog when trying to find database table naming conventions. Thanks for putting pieces together. Good works!

re: DevCampus Database Naming Conventions

Jerome — Wed, 03 Jun 2009 23:11:03 GMT

Nooooooooooooo. Database design 101 - table names absolutely should not ever, never be plural. It's a cheap and nasty practice and makes you look like a chump who doesn't take database design seriously.

If you end up with a Customers table that only has one record in it, do you rename it to Customer then? So you have a Baskets table and a Products table... is the detail table called BasketsProducts? Arrrggg, horrid! :)

re: DevCampus Database Naming Conventions

Douglas Swehla — Thu, 23 Apr 2009 23:44:55 GMT

@VM

Examples given in the Oracle resource linked below use plural table names. The copyright is 1996, 2005.

download.oracle.com/.../sql_elements008.htm

re: DevCampus Database Naming Conventions

VM — Wed, 08 Apr 2009 18:34:20 GMT

Stating that "tables are logical collections of one or more entities as records" contradicts the foremost rule of the conceptual and logical design (is the author aware of those??):

- a table represents an entity.

IT HAS BEEN A STANDARD FOR ALMOST ALL RDBMS FOR THE LAST 20 YEARS, INCLUDING ORACLE AND SQL SERVER THAT A TABLE NAME MUST BE IN SiNGULAR.

Update your knowledge.

re: DevCampus Database Naming Conventions

James Hargreaves — Wed, 01 Apr 2009 15:55:48 GMT

Interesting stuff!

I am not currently following a convention per se, but I intend to do so in the future.

I have always favoured pluralised table names, but I may switch to singular names in the future, as they seem like the way forward to me. For instance, my take on the project/activity example above would be:

CREATE TABLE project (

id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

name VARCHAR(100),

);

CREATE TABLE activity (

id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

name VARCHAR(100),

project_id INT NOT NULL,

);

So activity.project_id is a foreign key to project.id and your join would look something like:

SELECT project.name, activity.name

FROM project

LEFT JOIN activity

ON project.id = project_id

WHERE project.name = "world domination";

Which I think looks quite neat, with the possible exception of the RHS of the ON clause, which should probably be activity.project_id :)

re: DevCampus Database Naming Conventions

... — Thu, 12 Mar 2009 12:55:17 GMT

Gute Arbeit hier! Gute Inhalte.

re: DevCampus Database Naming Conventions

... — Thu, 05 Mar 2009 12:43:52 GMT

Interessante Informationen.

re: DevCampus Database Naming Conventions

Sebastian — Fri, 27 Feb 2009 14:27:48 GMT

Very interesting post & debate.

I agree to some of the rules and disagree about others. But in the end this is normal, there are lots of constraints and variables playing.

I'm not developing on .NET at this moment but i did, and i think it is interesting to make the db naming convention technology-independent, so i think it is important to not mix db naming with coding preferences.

Some points:

I used to pluralize table names according to what has been said here: Tables are the relational synonymous of oop's collections. But after facing the above mentioned problems i am making some concessions.

I disagree about the PascalCase notation, i like it and i use it in my code but i think it is not necessary to use it on the db. If you use an ORM framework, it eliminate the problem by creating a translation layer. You can still have a UserRole class but your table can be named actually user_role. I think it is highly recomended to use them whenever it is possible.

I had problems with UpperCases on some servers and now i think it is better to despense with them.

Of course, if you are going to have heavy Stored Procedures you will have a lot of code in your db but i think an ORMs and a good data access layer helps a lot.

Also i used to build up my junction tables names by joining the related tables names, but sometimes this aproach has the disadvantage of the neverending table names, so i am not sure about this but sometimes i try to find i meaningful name for the relation, for example: if i have a pair of car & car_identifier tables i prefer to name the junction table car_identification rather than car_car_identifier. Also i think it is always needed two have different symbols to distinguish between tables names and table name individual words (using PascalCase naming or not).

In the example above, using PascalCase naming you would name it: CarCarIdentifier, what i think it is not clear. If you use lower case & underscores the result would be: car_car_identifier wich isn't clear either. So i use a double underscore to separate table names from individual words: car__car_identifier. I know, this may look awful :), but i think in a priority order it is first the compatibility with external conditions, and i did not like when i had to change all my PascalCase named table & fields to lowercase because of some technical constraints.

So now, and for me: database & code are different things and i don't look for beauty in the db. I give priority to compatibility and order. It is only my point of view.

I find very useful this post and encourage everybody to build a stronger convention.

Great job.

Sebastian.

PS: Sorry for my poor english.

re: DevCampus Database Naming Conventions

SA — Fri, 14 Nov 2008 13:45:43 GMT

Hi, Why do you advice againt prefixing column names with the datatype ie intNumber och strName ?

Thanks

re: DevCampus Database Naming Conventions

Mark Brady — Wed, 18 Jun 2008 16:44:04 GMT

@ Tariq:

An HR/Payroll app? Really? that hasn't been done to death?

ff_mac said "Using tablenameID for both FK and PK gets rid of the name collision of the ID field but means that you can’t easily find FK references separate from PK references."

Huh? you have no constraint table to query that defines PKs and FKs. No it's not hard to tell the difference in any real RDBMS. What is hard is to find missing constraints left by sloppy developers when the column changes name from table to table.

Plural table names: I can always tell the people who never script changes to their databases. If you have a COMPANIES table is the ID column Companies_ID? That makes no sense. When you start to write code that builds code, you'll want this to be very, very regular.

%table_name% = Name of the table.

%table_name%_ID = Name of the PK Column

%table_name%_PK = Name of the PK Constraint.

If I had to guess at plurals it would make this process much more difficult.