Tuesday, March 23, 2010

Rant: Table Names Should be Singular!

There's two main schools of thought on how tables should be named: as singluar, or as plural. For me, the deciding factor is clear: English pluralizes nouns inconsistently!

Today, for example, I had two tables to create that were identical in all respects (don't ask). So, I created a fair amount of code to create the first table, with its assorted indexes, constraints, triggers, extended properties, etc. So far, so good.

Next, it's just a simple copy-and-paste, followed by a find-and-replace, to create the second table, right? No - not if the first table is named "Equipment", and the second is named "Tractors". Here's the problem.

This is a snippet of the "Equipment" table's code:

    EquipmentID INT,

So let's copy-and-paste and find-and-replace it, to create the "Tractors" table:

    TractorsID INT,

See the problem? "TractorsID"?? That ain't right - "TractorID" is what we want, obviously. But to get it, we have to edit each mistake by hand. So what should have been a five-second task is now an aggravating five-minute task.

The culprit in this example is the noun "equipment": what's the singular of "equipment"? "Equipment"? "Piece of equipment"? "Equip"? Although it's linguistically grotesque, this table would be better named "Equipments", just for the sake of clarity and consistency. Or, another noun, which pluralizes the same way as "tractor" does, should be used: "machine", maybe?

The root of this problem is that there are so many different ways to pluralize English nouns. According to this article, the most common way is to append an "s": one duck, two ducks. Or sometimes it's an "es": one potato, two potatoes. Or maybe an "ies": one pony, two ponies. Possibly a "ves": one calf, two calves.

And we haven't even gotten to the irregular plurals. Some nouns are the same when pluralized: one fish, two fish. Some add an "en": one ox, two oxen. Some add an "ren": one child, two children, or one brother, and some brethren.

Sometimes there's a mid-vowel change: one goose, two geese, one louse, two lice. Also, one crisis, but two crises.

And English has borrowed a lot of words from Latin and Greek, so we have: alumna/alumnae; matrix/matrices; criterion/criteria; phalanx/phalanges; addendum/addenda; hippopotamus/hippopotami; and schema/schemata.

Not to mention loan-words from: Hebrew (seraph/seraphim); French (château/châteaux); and Italian (graffito/graffiti).

Also, my personal favorites, nouns that don't even have a singular form: equipment, clothes, amends, etc.

There's much more, but the point is, name your tables as singular, not plural. Then the whole issue goes away!


Anonymous said...

Why would you use find and replace to change Equipment to Tractors, but use manually search and edit to change TractorsID to TractorID? I think this is really clutching at straws.

Anonymous said...

Accurate design is not clutching at straws; it's the difference between a rough board, and a finely sanded and polished surface. Much like Refinement is one of the main differences between a steel hammer and a wooden club.

Anonymous said...

I was just about to create an Equipment table and was feeling the same way.

Anonymous said...

Problem here with your tractor example is that the Tractors table is a collection of tractors. Each record is ONE tractor. Therefore TractorID would be correct. And since you would have multiple rows, you would have multiple instances, and therefore you would have multiple tractors, each with ONE ID, therefore Tractors can have a singularly-named ID (TractorID).

As far as Equipment goes, the word "equipment" is indeed ambiguous, but if you have a singular ID, EquipmentID, you can count Equipment as a "plural" table name (plural doesn't always mean "ends with an s", simply "more than one").

Post a Comment