Fog Creek Software
Discussion Board




Table Names:  Singular or Plural?

When modeling data, entities should have singular names - at least according to IE, IDEF1X, etc. For instance, from IEF1X:

IDEF1X

December 21, 1993

Section:3.1.2 Entity Syntax3.1.2 Entity Syntax

...The entity name is a noun phrase that describes the set of things the entity represents. The noun phrase is in singular form, not plural.  Abbreviations and acronyms are permitted, however, the entity name must be meaningful and consistent throughout the model...



There are those who insist that table names should be plural. Why, when transforming my logical model to the physical, would I complicate my life by transforming each entity name to a plural? My databases are usually very
complicated; the last thing I need is to have a different  name for everything. Furthermore, occasionally a client may not provide me with ERWin or another modeling tool, and I'll be forced to produce my ER Diagrams in something like Enterprise Manager which doesn't provide capability to have different logical & physical names - and ER diagrams just don't make any sense with plural names!

Love to hear what happens to other data modelers when there models are implemented.

Thanks!

--Dave

SZP
Friday, April 05, 2002

This is a religious issue, and I don't think it makes one tiny little bit of difference to anything. Personally, I like plurals because they make SQL sound more like English when spoken aloud, but neither one gets me passionate. I've worked places where one or the other was violently preferred, and it really just doesn't matter. At most places, there is not a real separation of model-vs-implementation, and the diagram IS the database.

I find the crowd that argues about such things is the style-over-substance crowd, like the OOP fascists and other children that see every problem through the same horse blinders their teachers strapped on them in college.

Troy King
Friday, April 05, 2002

I don't think it matters which way you go but pick one and stick with it. Let it be known that the standard is and enforce it. Having some tables singular and some plural is annoying and confusing.

It could be worse, the system I am maintaining has tables named tbco02 and tb1307. (The names were copied from older mainframe tables)

John McQuilling
Friday, April 05, 2002

I, too, firmly believe that this issue is simply religious, but other developers - both at my place of employment and in the developer's community at large - disagree.  Let me just add these thoughts:


(1)
As I said in my first post, it massively complicates my life to transform each entity name to a plural when transforming my logical model to a physical.


(2)
People are absolutely right when they claim that plural names are much more intuitive when examining tables from the perspective of a SQL programmer.  But, certainly, other professionals have different perspectives with which they are more comfortable.  If you're perceiving the tables as a data modeler would, it's my belief that plural names are terribly counterintuitive. 

Let me clarify.  There are two types of developers:  those who are more visual than textual, and those who are more textual than visual.  For the first group (those of us who conceive of our databases and object models *visually* as boxes in our heads connected with little lines and arrows) plural names are mighty confusing.  For the second group (those who prefer to analyze a system by digging into SQL scripts and source modules) singular names probably seem just as confusing.


After thinking about it this way, I really believe it's a matter of perspective.  But I know there are developers out there who feel differently.  Is there anyone out there who does *not* think that this issue is simply religious?

SZP
Saturday, April 06, 2002

I guess the whole distinction between "data modeller" and "sql programmer" seems pointless to me because of the nature of my job. I also design and code large databases, and I don't see the difficulty of creating it or envisioning it with plural or singular table names. How hard is to to say "what aspects do users have" vs "what aspect does a user have"? Good grief.

The design phase is eventually relegated to some printed diagrams, and then I spend months coding my stored procedures, queries, security, etc... so I just plan ahead on the model when possible.

The visual vs text bit ... It still boils down to someone in a code window typing the commands in. If your job is so specialized that all you do is draw boxes in a modelling app, then I guess I can't offer any advice. For me, the implementation takes a lot longer than the design in the case of databases, because a hundred tables means five hundred procedures by the time it's all done with.

Troy King
Saturday, April 06, 2002

In general, I like code which flows naturally, which almost always implies the plural form for collections. The deciding factor for me is my preference for using the same or very similar names for objects and the database tables they use - e.g. a User is an element of Users and is stored as a single row in the Users table.

I find this leads to more naturally flowing code:

foreach ($Users as $User) {
$Message->addCC($User->Email);
}

or

SELECT ... FROM Applications WHERE OperatingSystem=...

None of this is a major issue in its own right but I have found that people who always use the singular often make other, weightier annoyances such poorly designed objects or unclearly named variables. The mere fact that we're discussing this probably means we aren't in danger of that.

Chris Adams
Saturday, April 06, 2002

To think either singular or plural is wrong I think.

The correct way is surely to use both.

If we have a table full of thousands of customers then surely the table name "Customers" is the way to go.

The Customers table would just be ID, Name, etc, more details on the customer would be stored in a "Customer" table.

Much the same as in a common OO model.

Tony
Saturday, April 06, 2002

cha-ching
that'll be twelve cents, please...

the guy in the next cube
Sunday, April 07, 2002

This is a generic programming issue: it applies to almost any programming language.  The problem arises because both singular and plural make sense, depending on the context.

If you have an table/array of customers, then the name Customers flows best when you're thinking of the array itself, the set of customers.  But it breaks down when you try to access one customer:

foo = Customers[127]

foo isn't the 127th Customers, he's the 127th Customer.

The suggestion that you simply pick one and stick with it makes the most sense. My personal experience (with no data to back this up) is that programs tend to refer more frequently to individual items from a table/array/list than they do to the table/array/list itself, so my own rule is to use singular.

Chris Dunford
Sunday, April 07, 2002

> This is a generic programming issue [...] But it breaks down when you try to access one customer

struct Customer;
typedef Customer Customers[256];
...
Customers customers;
Customer& customer = customers[127];

I prefer a plural for table names: Customers, CustomerData, CustomerRecords (or, CustomerTable).

> Is there anyone out there who does *not* think that this issue is simply religious?

A relationship or constraint (little lines and arrows) between tables is a relationship between records, or between each record, in the table: so I don't see why a plural should be massively confusing ... but if I were doing a code review, I wouldn't require the developer to rewrite whichever.

Christopher Wells
Sunday, April 07, 2002

I don't think plural or singular really bother me.

It would bother me if some tables were plural and some singular. Both styles in one application would make it harder to know which form a table has unless you always kept a schema diagram lying around.

I used to think that plural names made more sense, but then I wrote a small sql generator to create defintions based on the class files I wanted to persist. It seems a lot easier to just keep the singular name from my classes. One of my persistent objects is named Supply - should the plural table name be Supplys or Supplies?

So now I think that singular is probably the best. I don't need to program a generator with all the rules to make a word plural.

Nathan
Sunday, April 07, 2002

Entity names singular, table names plural.

Yes its religious, but I operate in a broad church and would caveat my statement with "unless you have another standard". The important thing is to have a standard and to stick to it. What would your c code look like if you randomly changed the indent from three to four to five characters throughout your source?

Don't mix, and certainly don't change your mind half way through a project.

Andy Todd
Sunday, April 07, 2002

I feel this is more of a "bug" with the english language.

A lot of times you just want your SQL to look somewhat consistent. But it's difficult because of various ways of expressing plurality in english still sometimes doesn't give you a consistent "look and feel" in your SQL statements.

The solution a some coworkers and I cooked up was to simply do something along the lines of <singularname>_T, so we'd end up with statements along the lines of "SELECT FIRST_NAME, LAST_NAME FROM USER_T". In this way, we avoid dealing with the various plural suffixes that exist in english, and along the way get a free way to grep through our source code for SQL statements.

James Wann
Sunday, April 07, 2002

Next on Joel - Putting your socks on: Left foot or right foot first?

DB
Monday, April 08, 2002

>SELECT FIRST_NAME, LAST_NAME FROM USER_T

I don't have a strong opinion one way or the other regarding singular vs. plural table names but I HATE underscores!  Of course that's another argument . . .

I'm looking at one of my schemas right now and I used a combination of singular and plural:

Orgs (plural) contains all the associated organizations.
TSHeader (singular) contains the Timesheet headers.  I suppose I prefer it to TSHeaders.

Dammit, now I'm torn.  I strongly believe that standards are a good thing, but I also believe in the old Emerson saying "A foolish consistency is the hobgoblin of little minds".  I guess there's a fine line between maintaining a standard and just being anal.

Sonny
Monday, April 08, 2002

Damn I haven't used that Emerson quote yet this year and you've beaten me to it.

If I have any standard at all, then I will use plural names for tables that have n-ary relationships with some main table or where I'm using a master link table for a many to many relationship.

For instance, I have a single Address Table, with all of the properties that an address can have.  Because my other entities, Customer, Supplier, Carrier, etc all have addresses and its feasible for them to have the same address, I keep a Master Link Table Addresses which in most cases is a two column table with the owning entity ID on the left and the Address ID on the right.  (I'm a simple soul and like reading things from left to right).

Simon Lucy
Tuesday, April 09, 2002

Plural is clearly the way to go.
Imagine a class:
Users {
  User {
    FirstName
    LastName
  }
}

Users is the table and User is the missing encapsulation of most relational databases because it cannot define the complex class.  Just because the database cannot define a complex User type to be used for Users does not mean the Users table collection is a User type.  It is a User[] type. 

You might want to mistake database modeling to imply:
Users {
  FirstName
  LastName
}

but really it is doing
Users {
  row/item/record {
    FirstName
    LastName
  }
}

Some databases enable complex type definition, so if you name your table User, you will have it of type User, and you will have to rename the User table to Users.

A table is a collection of rows, not a definition of an object oriented type.  That is something else that can be done on advanced databases of now and the future.  A table/collection is a table/collection is a table/collection.

I wish the object oriented puriest would stop trying to do it because it makes their life easier because they cannot think far enough to see that there is an implicit single class definition.  The relationship lines in database diagrams are relating the fields in this implicit User type.

.Net Architect
Friday, November 21, 2003

I know this thread is old but it just became an issue to me.  I personally prefer plural names for tables.  But I just took a practice test for the MCSD 70-300 exam, and they asked that question.  I went for the plural answer, and I got it wrong!  Their explanation was along the lines of the orginal post in this thread (it is easier to map from ORM diagrams).  Now, I also don't think it should matter much which way you go.  But it seems that it does matter to Microsoft (well, maybe just to MeasureUp, which put together the mock test).

Ken Hales
Monday, April 26, 2004

Again - Old Post!  - but I need to speak to the issue - It's not religious, and its not style.

This goes back to good ol' RDBMS design (remember your first course in database technology back in college) and use of proper language when referring to objects and models within this context - MS-SQL programmer or otherwise!

A table is an object or entity (singular) that represents a group of related entities (plural), that is, it is an entity set - so it is a single object, and therefore your table should be given the name of the entity set you are trying to model.  You do not name your dog Cliffords, he is one dog, an entity, an object, you name him Clifford.  The relationship (1 to M, M to M, etc.)between two entity sets is what determines the plurality of the entities invloved.  You don't say to yourself I want to select a "customers" object from the "customers" table!  You say I want to select a "customer" object from the "customer" table. 

Take it a step further, if you are referring to a column name in a table during a join and you have named your table "Customers", then joining on Customers.CustomerId = OtherTables.Id makes absolutely no sense - you should be joining on Customer.CustomerId = OtherTable.Id.

Now think how is a "Customer" a "Customers" object?  See it does not make sense when put in those terms.

And I have to agree with the ERWin guy that modeling a database using plural table names is nasty, in that it can become confusing what entities are actually represented by the tables in your schema especially when you have many tables to work with.

And it's not a religious issue - its a good design and best practices issue, an example of a religious issue is what is happening in the Middle East.

You will do your self good by picking up a book on the subject of relational database design and educating yourself on the subject.

Aaron Ramirez
Friday, May 28, 2004

*  Recent Topics

*  Fog Creek Home