Saturday, December 11, 2010

Plural table names are one trick pony

English plurals are not ORM-friendly.

Is that a proof by blatant assertion? It is if you can assert otherwise ;-)

Case in point, Chinese have two form of plurals, one for person, another for non-person, you use "men" suffix for person, "henduo" prefix for non-person. Filipinos plural form for everything(person or non-person) is facilitated by using "mga" prefix


SELECT * FROM EmployeeMen


SELECT * FROM HenduoFruit

SELECT * FROM HenduoMouse


SELECT * FROM MgaEmployee





SELECT * FROM Employees

SELECT * FROM Children



See? Mice and Children are not directly mappable to ORMs. And any decent ORM without an AI of sort, will not be able to infer that Mice's singular form is Mouse. In Filipino and Chinese languages, pluralizers are consistent, more amenable to ORMs

Let's say you are in a radical programming environment and the management forbids you from using mnemonic aliasies...

SELECT E.Name AS EmployeeName, C.Name as ChildName
FROM Employees AS E
JOIN Children AS C ON C.ParentId = E.ID will find AS keyword a glorified singularizer for table names:

SELECT Employee.Name AS EmployeeName, Child.Name AS ChildName 
FROM Employees AS Employee
JOIN Children AS Child ON Child.ParentId = Employee.ID

And let's say you are in a very very extremely radical programming environment, they won't allow you to use aliases...

SELECT Employees.Name AS EmployeeName, Children.Name AS ChildName -- ugh, those look odd
FROM Employees 
JOIN Children ON Children.ParentId = Employee.ID

..., your queries will look odd.

BTW, we programmers are good in switching mental model, we don't really read this...

box.setX( box.getX() + 1); set the X based on its old X with 1.

we read that as:

box.X = box.X + 1;
box.X += 1;

And the real best programmers are the ones that are trying to infer the intent of code given the lack of comments or proper method name, they don't go through the motions, they infer intents, they read the code above as: Move the box to the right

The best programmers are the ones with mental models that makes them able to abstract a concept very well, with or without the first-class constructs on syntaxes.

So the best programmer, would really read this...

SELECT * FROM Computer 


SELECT * FROM Computer(s)


And since I'm a Filipino programmer, prefixing words to entity to make them plural easily gets old...

SELECT * FROM MgaEmployee




...and almost akin to incessant prefixing of tbl to table names:

SELECT * FROM tblEmployee

SELECT * FROM tblChild

SELECT * FROM tblFruit

SELECT * FROM tblMouse

Naming tables in plural forms are one of the design patterns that is like a novelty, aside from they easily wear off, they are just a one-trick pony. Plural names only work well if you will query one table only; reading SELECT Lastname, Firstname, Birthdate FROM Employees gives one a warm-and-fuzzy feeling; but the norm is, we join tables, and aliasing table names are warranted, especially to plural-advocates. To plural-advocates, wanna bet you would give your tables aliases rather than going through the motion when joining tables? i.e. This feels icky to you despite your plural-form-fervor for naming tables...

SELECT Persons.Name AS PersonName, Countries.CountryCode 
FROM Persons JOIN Countries ON Countries.CountryID = Persons.BirthCountryID

... Why that feels icky to you? hint: because to any self-respecting programmers, any selected columns with table qualifier would look odd if the table qualifier is of plural form.

With plural form table names, you can't seamlessly plug-and-play them to other components(think ORM), can't(or won't) leave them alias-less when querying two or more tables(looks funneh):

SELECT Children.Name AS ChildName, Children.Age, Fathers.Name AS FatherName 
FROM Children 
JOIN Fathers ON Fathers.ID = Children.FatherID)

So next time you encounter advocates of singular names, don't argue with them, they are mostly right.

And if you don't want to flunk MCSD 70-300 exam, use singular names

In terms of ORM-friendly plural naming conventions, here's their rankings:

0. Singular (hey, you said ORM-friendly plural naming conventions! how this manage to sneak in here?)

1. Filipino plurals

2. Chinese plurals (words are immutable, you just prefix "henduo" or suffix "men")

3. English plurals (that's why this article exist ;-) ) Aside from suffix "s", you have irregular and mutable plural forms (child -> children, mouse -> mice, goose -> geese, moose -> moose( not mutable, i just want to expose the inconcistencies of the language ;-) ) )

So keep in mind folks, plural table names are one trick pony only, the only instance it could give you a warm and fuzzy feeling is when you are getting data from one table only.

SELECT Name, FavoriteLanguage, FavoritePizzaBrand FROM Programmers

When joining tables with plural names, pleasant-wise, all bets are off

English plural 101:

[UPDATE March 11 2011]

My desire to name a DbSet on Entity Framework was not honored, EF tried to look for People table for my public DbSet Persons, need to override things on OnModelCreating :-)

No comments:

Post a Comment