Mysql Naming Convention

Gregory Hill Gregory_Hill at tni.com
Tue Apr 18 16:54:56 MDT 2006


Well, that's probably the best argument I've heard in favor of plural
table names, excluding the 'show databases' example.  It's still a
flawed argument, though.  If SQL was supposed to be a totally natural
language, 'JOIN' would be 'JOINED WITH', 'ORDER BY' would be 'ORDERED
BY', etc, etc.  I mean, how natural is 'LEFT OUTER JOIN' anyway?  

Is there really a huge readability difference between these two queries:
SELECT u.*, l.* FROM user u JOIN level l USING (level_id) WHERE
user_id=?
SELECT u.*, l.* FROM users u JOIN levels l USING (level_id) WHERE
user_id=?

At its best, SQL is broken English, in which case, singularity or
plurality of table names is irrelevant.  In reality, it reads more like
a programming language than a natural one, so the point is even less
relevant.

Anyway, as was pointed out, consistency should be the primary concern.
If you want pluralized table names, and all the programming grief
associated with it, just make sure you do it everywhere, and make sure
you have some good logic in place for pluralizing and singularizing
(remember, sometimes you have the class name and need to know the
associated table name, and other times it's the opposite).  If you want
a simplified framework, go with singular table names, and all you need
is a simple function to change a capital letter to an underscore
followed by the lowercase version of that letter, or to reverse that.

Imagine if you were building a database for a zoo.  I've got this Goose
class and this Moose class, but Goose belongs to the geese table and
Moose belongs to the moose table.  Have fun.

Yeah, it's an unrealistic example, but less extreme examples are common,
and sticking to singular table names in the beginning makes them
non-existent.

Greg

> -----Original Message-----
> From: plug-bounces at plug.org [mailto:plug-bounces at plug.org] On Behalf
Of
> Mark Hobson
> Sent: Tuesday, April 18, 2006 4:22 PM
> To: plug at plug.org
> Subject: RE: Mysql Naming Convention
> 
> Plural is the right way.  SQL was designed to be a natural language
(thus
> the select col from tablename where...)
> 
> Thus, a class represents a row in a table...a single row, whereas the
> table
> represents a collection of rows (plural).  Therefore, the table name
is
> plural, the class name is singular.
> 
> If you are using a framework, it should have a small function to
convert
> from plural to singular.  You'll have to anyway, since at some point,
> you'll
> need to have a plural word somewhere (i.e. "List of Clients" for the
title
> of a page, that queries the clients table).
> 
> MySQL even has built in functions that are plural:
> 
> Show databases
> Show tables
> 
> Mark 



More information about the PLUG mailing list