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.

@konrness
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.

@stuardo_str
namespaced_under_scored

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

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

@calevans
single table names, camleCase attributes and PK is always id

@maphpia
namespaced_underscore_name, singular, and ‘id’ as PK

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

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

@avalanche123
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.

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

@brodrigu
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?

Cheers!

Comments