Using plurals in database and models

Not for 'how-to' coding questions but PHP theory instead, this forum is here for those of us who wish to learn about design aspects of programming with PHP.

Moderator: General Moderators

mkz
Forum Newbie
Posts: 11
Joined: Tue Oct 05, 2010 10:37 am

Using plurals in database and models

Post 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.
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Re: Using plurals in database and models

Post 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'
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: Using plurals in database and models

Post 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:
(#10850)
alex.barylski
DevNet Evangelist
Posts: 6267
Joined: Tue Dec 21, 2004 5:00 pm
Location: Winnipeg

Re: Using plurals in database and models

Post 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
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Re: Using plurals in database and models

Post 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.
User avatar
Jenk
DevNet Master
Posts: 3587
Joined: Mon Sep 19, 2005 6:24 am
Location: London

Re: Using plurals in database and models

Post 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"
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Using plurals in database and models

Post 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
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Using plurals in database and models

Post 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" ?
There are 10 types of people in this world, those who understand binary and those who don't
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Re: Using plurals in database and models

Post 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)
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Using plurals in database and models

Post 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.
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Re: Using plurals in database and models

Post by Weirdan »

VladSun wrote:That's the worst naming convention IMHO.
How about tbl_users_1 then (manual partitioning)?
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Re: Using plurals in database and models

Post 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.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Using plurals in database and models

Post 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]
There are 10 types of people in this world, those who understand binary and those who don't
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Re: Using plurals in database and models

Post by josh »

Either is acceptable for me, the latter is preferred if 'membership' is an object or concept in the application code.
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Re: Using plurals in database and models

Post 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.
Post Reply