PapayaSoft - Phuket web development

Naming Conventions for Database Tables and Fields

| Comments

@JeremyKendall recently asked a great question on Twitter:

Do you have a DB naming convention? camelCase? under_scores? Singular or plural table names? I can never settle on anything.

He got some great answers. Since I will certainly need to reference these opinions at some later date – and Twitter will not keep the history forever – I am recording them here.

I find it’s easier to follow my PHP code’s naming convention, so: camelCase for table names and columns. Under_score for DBs. Oh yeah, I usually do plural table names.


i once read that “best practice” is singular and underscores. but as long as you’re consistent in the db, you should be good.

CamelCase (ucfirst) for tables, and pre_desc for columns. User.u_id, UserProperties.up_value… It’s not perfect but it works

single table names, camleCase attributes and PK is always id

namespaced_underscore_name, singular, and ‘id’ as PK

for us; id is always tablename_id to avoid name conflicts between joins. We use underscore (oracle). Singular names.

Plural table names; lowercase, underscore separated field names; PK is always id.

lowercase underscore plural table names, id is the PK, _id is the FK to table name

@h (which is an awesome Twitter handle)
Singular Table names, first char upper case. Allows you to create models that match exactly.

Depends on the project, but I hate camelcase, and if I’m doing models/objects, I generally do plurals for table names.

plural under_scores ftw!

I actually chimed in with my own support for @avalanche123′s suggestion: lowercase underscore plural table names, id is the PK, _id is the FK to table name. Semantically, I find this compelling since you end up with SQL queries of like “SELECT * FROM users” which reads better to my eye than “SELECT * FROM user”.

The only downside to this is that dealing with irregular plurals can sometimes be a pain. Consider a table containing properties. As noted above, I prefer the semantics that follow from calling that table “properties”. But a convention for a FK into that table “<singular_table_name>_id” would yield a field named “property_id”. The disconnect between the singular and plural can sometimes be problematic. Sure, there are Inflector classes (like this one) that know enough about English pluralization rules and their exceptions to do the mapping, but it’s one more hassle. So there are times when I bite the bullet, abandon the superior semantics, and simply hold my nose and go with singular table names.

What are your preferences and rationale?