Page 1 of 2

Using plurals in database and models

Posted: Sun Oct 17, 2010 10:51 am
by mkz
I don't quite get the convention of pluralizing database table names.

Is this a convention that has carried over from Ruby on Rails (and continued with projects like CakePHP, Symfony) or did it stem from something else?

I name my tables in the singular. I have a "user" table, and an "article" table, etc.

My reasoning: a table is, by nature, a collection. So I consider it an "article table" just like you might have an "article list" or "article array" in your code. You also use an "article class" even though we all know that one class is instantiated by many objects. "Class" is a word that means a set of things sharing common properties, so it's not really semantically far from the notion of a table. Yet, by using the singular for class names, we are using a convention that simplifies things. We don't need two separate strings, one for the class and one for instances. Similarly, it's easier to have one string that applies both to table rows and to the tables themselves.

Okay, so perhaps it sounds better? Or it sounds more "human". Not a good enough reason. To me, a method called getAllArticles() sounds just as good as getEveryArticle() or getArticleList(). My point is that sounding better can be achieved without pluralizing things.

And yes, people seem to go through a lot of trouble. I almost fell off my chair when I saw CakePHP's "Inflector". It's a class that deals with pluralizing words, among other word transformations.

It's 600 lines just to deal with this silly convention! Take about jumping through hoops. It uses a bunch of regular expressions, and then big lists of exceptions to the rules, in an attempt to cover everything. So Person becomes People, Man becomes Men, etc.

Here's an excerpt from the class. (see the whole thing. but put your coffee down first)

Code: Select all

 var $_plural = array(
42 	'rules' => array(
43 	'/(s)tatus$/i' => '\1\2tatuses',
44 	'/(quiz)$/i' => '\1zes',
45 	'/^(ox)$/i' => '\1\2en',
46 	'/([m|l])ouse$/i' => '\1ice',
The guys at CakePHP have thought of everything. I can imagine it now... "CREATE TABLE oxen ..." and CakePHP has your back!

Conclusion: when a convention requires you to write and maintain a fickle and language-dependent 600-line class, it's not a good convention.

Re: Using plurals in database and models

Posted: Sun Oct 17, 2010 1:03 pm
by josh
I use singular names. When you're doing a join it doesn't read naturally if the table names are pluralized.

select * from users, bugs where users.id = bugs.user_id
vs
select * from user, bug where user.id = bug.user_id

I prefer the latter because my brain thinks in terms of 'joining this row to that row', not in terms of 'interweave this collection with that one'

Re: Using plurals in database and models

Posted: Sun Oct 17, 2010 8:07 pm
by Christopher
I think pluralizing like Ruby on Rails is one of the most annoying hipster programmer styles around.

And I agree with Josh that singular is simpler and better, but I find most programmers use plurals so I have trained myself to not care. :drunk:

Re: Using plurals in database and models

Posted: Mon Oct 18, 2010 10:37 am
by alex.barylski
One more vote for singular tables names. :)

Another issue I have with plural table names is encountered when you have table names like categories and then having an object named category, IME, just feels awkward. Keeping both singluar makes for greater consistency and readability, IMO.

Cheers,
Alex

Re: Using plurals in database and models

Posted: Mon Oct 18, 2010 11:09 am
by josh
Yeah, inflection (plurals) can be a lot worse when you're not speaking English apparently. In some languages a word can be inflected in 100s of ways, Ex. 'run' writen as (runners, running, runs, run, ran, runner, etc..) Its most straight forward to "stem" the word and use its simple form. You'll stay more consistent this way.

Re: Using plurals in database and models

Posted: Tue Oct 19, 2010 5:11 am
by Jenk
I've always used plurals. Probably because I don't use names like "user list/collection" either. If it's a collection of users, it's exactly that "users" :P or perhaps something more contextual like "usersToBeUpdated" or "articlesToBePublished"

I think it just reads better when I'm doing a "select * from users where id = 123" compared to "select * from user where id = 123"

Re: Using plurals in database and models

Posted: Tue Oct 19, 2010 5:37 am
by Eran
I'm with Jenk on this one. The table contains "Users" not one "User" so it should be reflected in the name. But really, it's personal preference

Re: Using plurals in database and models

Posted: Tue Oct 19, 2010 9:39 am
by VladSun
Jenk wrote:I think it just reads better when I'm doing a "select * from users where id = 123" compared to "select * from user where id = 123"
How should I translate "users.id = 124" ?

Re: Using plurals in database and models

Posted: Tue Oct 19, 2010 10:50 am
by josh
Yeah when you read "users.id =123" in your read do you read it as "user's" (possessive) or users (plural) Because when you are specifying a a primary key the query's result set is in fact not a collection (well its a collection of one user, I'll give you that... but if a "collection of users" is crazy a "collection of user" is even crazier )

I want to establish though that this is really trivial. This is probably the last thing I'd worry about when approaching a new code base. What would tick me off more is if you didn't prefix your table names with the name of the modules they are depended on by, so that the tables for a given module are all intermingled with 100s of other tables so you can't find the darn stuff you're looking for.

Really, the table prefix gets on my nerves more than the suffix. I think the only time plurals becomes a real problem is when you're not consistent with yourself, or can't explain an inconsistency in a rational manner. (Example: "oh I named this table plural but not this one because ________" - there is really no good reason)

Re: Using plurals in database and models

Posted: Tue Oct 19, 2010 11:33 am
by VladSun
josh wrote:Really, the table prefix gets on my nerves more than the suffix.
How about:

Code: Select all

select * from tbl_users ...
:evil:

That's the worst naming convention IMHO.

Re: Using plurals in database and models

Posted: Tue Oct 19, 2010 11:55 am
by Weirdan
VladSun wrote:That's the worst naming convention IMHO.
How about tbl_users_1 then (manual partitioning)?

Re: Using plurals in database and models

Posted: Tue Oct 19, 2010 12:08 pm
by josh
Yep thats exactly what I'm remembering. Half the tables were named like that and half were not.

Sometimes two tables would be coupled (tbl_users and users_photos). One would be prefixed the other not. Made it so difficult. Database refactoring quickly took care of that, but that consumed about 35% of my time, just fixing the database & schema. These inconsistencies were at all levels, even column names were wacky. In the course of 60 days I wrote something like 500 database re-factoring scripts, to fix only a 50 table database. Most of them were just renaming stuff.

What I did was just refused to develop the old site out, we set up a new site the database refactoring scripts copied tables over to the sandbox, and renamed away. Anytime we wanted the old database converted to the new format, all data in-tact, I just ran a command. That guy never launched the site in the end due to feature creep & no budget, but the idea was right before launch we'd run this command & then point his document root to the new code base & database.

Re: Using plurals in database and models

Posted: Tue Oct 19, 2010 2:15 pm
by VladSun
While standalone tables are relatively easy to be named (user, group, etc.), relational tables are not. What it should be :
[text]table user_group
user_id
group_id[/text]
or
[text]table membership
user_id
group_id[/text]

Re: Using plurals in database and models

Posted: Tue Oct 19, 2010 3:36 pm
by josh
Either is acceptable for me, the latter is preferred if 'membership' is an object or concept in the application code.

Re: Using plurals in database and models

Posted: Thu Oct 21, 2010 9:48 pm
by John Cartwright
josh wrote:Either is acceptable for me, the latter is preferred if 'membership' is an object or concept in the application code.
Me too. However, if I was to start a project from scratch, I would go singular (just my preference, no real reasoning behind it). As for existing projects, as long as things remain consistent I have no problem with either preference.