Saturday, September 6, 2014

DB Table Name Plural or Singular? How To Choose

A common question for web programmers is whether to give database table names singular or plural names.  Since the intuitive preference for one or the other is largely a religious debate, in this post I help you compare the two across many use cases you might not otherwise consider.  Then I let you decide which works better over the long haul.

Below I present a comprehensive list of use cases that will matter to you as you use your database tables.  For each case, I present a table showing examples of singular (left side) and plural (right side).

Case 1: Reserved SQL words

Everybody needs a table for user accounts, and the intuitive name for such a table is "user(s)".  But the singular form of user is a reserved word in SQL, so it must be escaped if it is used for a table name.
Singular Plural
`user`or user_account users

Case 2: Programming Language Symbols

The intuitive name for an array is often plural, which doesn't follow from a singular table name.
Singular Plural
$notes = $db->query("SELECT * FROM note")
foreach ($notes as $note) ...
$notes = $db->query("SELECT * FROM notes")
foreach ($notes as $note) ...

Case 3: Irregular Plurals

Irregular plurals don't go well with systematic table naming.
Singular Plural
address
cactus
user
potato
sheep
story
addresses
cacti or cactuses or cactuss
users
potatoes or potatos
sheep or sheeps
stories or storys

Case 4: Compound Table Names

Plural table names don't combine intuition and consistency well when forming compound names.
Singular Plural
address
note
sheep
user
user_note
user_address
user_sheep
addresses
notes
sheep
users
users_notes or user_notes
users_addresses or user_addresses
users_sheep or user_sheep

Case 5: Derived Column Names

Singular Plural
user_id
create_user_id
users_id or user_id
create_users_id or create_user_id

Case 6: Alphabetized Listings

Plural names and compound names don't alphabetize well together if you aren't totally consistent about using the plural.

Singular Plural
user
user_address
user_note
user_sheep


user
userAddress
userNote
userSheep
user_addresses (vs. users_addresses)
user_notes
user_sheep
users


userAddress (vs. usersAddresses)
userNote
users
userSheep

I will resist the temptation to draw any conclusions.  For conclusions, I recommend the very-highly voted SECOND answer and its FOURTH comment (NOT the awarded answer) to this question at StackOverflow.

No comments: