All the Perl that's Practical to Extract and Report

Database Naming Conventions

grantm on 2008-04-22T09:19:52

A $cow_orker recently sparked a debate about conventions for naming database objects. Obviously this is a bit of a religious issue for many and we certainly uncovered a variety of opinions. One very basic question which many feel strongly about is the pluralisation of table names. I have a preference for singular but am happy to run with plural if that's the convention in an existing project.

Early in my development career I saw a colleague ridiculed for creating a database table with a pluralised name. His justification was (quite reasonably) "I called it 'widgets' because I want to store multiple widget records in it". The DBA's response was "Of course you want to store multiple records in it. If you didn't have multiple records you'd hardly go to the bother of creating a table, would you?". From this logic it comes down to a simple choice: make every table name plural; or, don't bother. I've standardised on "Don't bother".

The thing I don't get is the vast number of people who subscribe to this inseparable pair of rules:

  • Database table names should always be plural
  • Object class names should always be singular

It seems obvious to me that if you agree with the first statement then using the same logic you should disagree with the second. Apparently other people don't see it the same way.

It seems to me that a 'widget' table defines the characteristics of a widget record and serves as a container for such records. Similarly a 'Widget' class describes the characteristics of a widget object and serves as a template for such objects. I just don't get why so many people see these two issues in black and white as obvious opposites.

__

Abigail on 2008-04-22T12:58:31

I don't get the connection between table names and class names. I don't care whether a table uses a singular or a plural name, but I don't follow that if you use plural table names, you should use plural class names as well. A table is a collection - a class is a type. I think one should apply the same rules for naming tables as one should use naming arrays. If you use some reasoning that table names should always be plural (or singular), then the same reasoning should give you all plural (singular) array names.

I go either way for tables and arrays. But my class names are usually singular, unless their instances contain a collection, and there's no good collective noun.

Tables vs Classes

grantm on 2008-04-22T21:28:00

I don't get the connection between table names and class names.

The english word class (in its generic non-CS use) derives from the same root as 'classification'. Dictionary definitions for class use words like 'a collection of things sharing a common attribute', 'A group of individuals ranked together as possessing common characteristics'. In this respect, a class is a collection in the same way that a table is.

I agree 100% that class names should usually be singular. It seems obvious to me that the same logic behind that decision applies in exactly the same way to database tables. But clearly I have failed to articulate this obviousness :-(

foot vs. feet

janus on 2008-04-22T13:06:45

I'm also in favor of singularity, mainly because pluralization is not generally and uniformly possible:

head vs. heads: simple
foot vs. feet: not as simple

Here, no generalized form is possible, thus it can't be done without explicit naming.
That of course costs time as you need to think about it every time you encounter it, especially if your mother tongue isn't english.

but common use cases differ...

rjbs on 2008-04-22T13:27:27

In SQL, the minimum likely use case is:

SELECT id, name, alignment FROM characters;
With a class, the minimum likely use case is:

object = Class.new;
I don't think anyone who uses other than the pluralization that I use deserves a painful death, but I also don't see that the number of tables and classes needs to be tied.

Re:but common use cases differ...

grantm on 2008-04-22T21:41:45

In SQL, the minimum likely use case is:

SELECT id, name, alignment FROM characters;

And each row you get back is made up of those attributes of a 'character'. Sure you get multiple rows, but I don't get how that's different from defining a class in order to instantiate multiple objects.

With a class, the minimum likely use case is:

object = Class.new;

A common case in Rails is:

@character_list = Character.find(:all)

Which ultimately generates SQL along the lines of:

SELECT * FROM CHARACTERS

And the application will then instantiate one Character object for each row retrieved from the table. In this case there is a clear and deliberate one-to-one mapping between an instance of the class and a row in the table and yet different rules are used for deriving a name for the container. That's the bit I don't get.

And yeah - I'm not planning to burn anyone at the stake for plural table names. The point of my post was to question why people apply different rules to what appear to be analogous situations (at least to my eyes).

one more thing

mattk on 2008-04-22T14:20:55

Plurals seem more natural to me, though I don't really care one way or the other. I do hate CamelCase names though, especially when it comes to ones like AccountPlanOptionLinkSeq...

Plural bad!

autarch on 2008-04-22T14:56:37

I really hate plural table names. There are two common problems plural table names have. First, as janus mentioned, plurals in English can be irregular. A common real-world example is "people" and "person". If I have a table named "People" I'll probably end up referring to "person_id" as well, which just irritates me.

Another problem is that it degrades badly when you have "linking" tables. Say I have Accounts and Users, then I might have an AccountsUsers table. Yuck! If I have Accounts, Users, and Roles, then I could have AccountsUsersRoles. Double yuck! And if that table needs it's own surrogate key, is it accounts_users_roles_id? Pure madness.

Re:Plural bad!

grantm on 2008-04-22T22:21:27

The proposed standard naming convention from $cow_orker also mandated the use of CamelCase. With the added wrinkle of a short suffix on every table name. E.g.: Account_act or ProductCommissionGroup_pcg. Any advance on Triple-Yuck ?

Re:Plural bad!

sigzero on 2008-04-23T02:07:58

I just barfed on my keyboard...thanks.

Re:Plural bad!

autarch on 2008-04-23T04:11:20

I do like camel case for table names, as a way to distinguish them from column names. This is very similar to the the Perl standard of camel case for package names.

That suffix thing is truly awful, though. What possible purpose could it serve? It's like naming everything twice.

Re:Plural bad!

grantm on 2008-04-23T06:59:25

The aforementioned proposed standard justified camel case for table names in the same way - to distinguish them from column names. I found that argument a bit spurious since a) I don't recall ever confusing column names for table names and b) the table names are easy to identify already - they're the words following the FROM and JOIN keywords :-)

Re:Plural bad!

btilly on 2008-04-23T11:21:35

It seems that most databases make table names case insensitive unless you go out of your way to use " everywhere. So using camelCase becomes an unenforced convention that is then not maintained. You also have the potential problem that a given name could be broken into English words in multiple ways, making capitalization ambiguous.

This is why I prefer using _ instead.

For the record, note that my job involves a lot of reporting. I spend more time writing SQL than Perl.

Re:Plural bad!

Abigail on 2008-04-24T12:40:47

It seems that most databases make table names case insensitive

Yeah, except for a particular free-of-charge database that seems to be quite popular. Tables are files on the underlaying filesystem, and if the underlaying filesystem is case sensitive, your tables are as well. Columns, OTOH, aren't case-sensitive.

Re:Plural bad!

runrig on 2008-04-24T15:12:36

Yeah, except for a particular free-of-charge database...

...and a certain well-known commercial database (*cough* Sybase *cough*). There is a global switch you (i.e. the DBA) can flip to change that, but then all havok breaks loose...