Table names should be singular

The Rails convention is for database table names to be plural. The Handmark convention was previously the same. However, our new DBA (Chris Simonton for those of you who listen to the show) changed that convention to singular. The reason for the change lies in a common misconception about database tables.

Most people think about a table as a collection of rows. The database is a place to store data, and the data is organized in rows. The rows live in the tables. Seems pretty straight forward.

But that line of thinking leads to some serious mistakes. A collection is a group of things that all belong together. Like a group of employees who work for the same company, or a group of line items on an invoice. But a table contains rows that are completely unrelated to one another.  The employee table contains employees of various companies, and the invoice line item table contains information about several invoices.

Thinking about the table as a collection of rows leads to an assumption that the rows are somehow related. We might assume that all employees in the Employees table work for the same company, or that all of the rows in the LineItems table have a common vendor. After all, the application will be hosted by that company or that vendor, right?

Applications may start out in one scope, but they tend to outgrow their initial design. If we allow limitations to creep in because of inadvertent assumptions, it may be difficult to make the jump when we need to. Assuming that there is a relationship among the rows of a table can cause you to forget foriegn keys. After all, if every employee works for the same company, why would you need a CompanyID column in the Employees table?

We made that mistake at Radiant. The Enterprise product hosts data for restaurant companies. Since the product started out as a targeted system for a small number of customers, we made the assumption that each company would have its own database. As a result, none of the tables contained a CompanyID. The database name itself identified the company.

In the days when a new company signed on about once a month, it was no big deal to prop a new database from the model. But as the product grew, we were adding companies more frequently. And as we made changes to the product we had to deploy those changes to each company individually. But because of the assumption, we were unable to merge companies into a common database when we recognized that it would be a good idea.

A database table is not a container. It is a type. It is not a collection of rows, but a collection of columns. It defines not a set of data but a set of relationships. It is alalogous to "class Employee", not to "private List<Employee> _employees". To remind us of this, database names should be singular.

Leave a Reply

You must be logged in to post a comment.