Archive for the ‘Standards’ Category

Table names should be singular

Thursday, May 24th, 2007

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.

Protocol Monopolies are Dangerous

Monday, April 23rd, 2007

The recent blackout of BlackBerry service has taught us a few lessons. Paul McNamara points out the importance of transparency. Merlin Mann warns us about techonogy addiction. But there is another lesson that I'd like us to learn from this experience: protocol monopolies are dangerous.

Almost 3 years ago, NTP sued RIM over their infringement of its push-email patent. The case dragged on, and threatened to terminate the service that many consumers had come to rely upon. RIM eventually settled with NTP for $612 million, ensuring that the service would continue.

Until this recent interruption, that is.

If not for the government sponsered monopoly that we call US patent law, competing services would have been available. RIM's customers would still have suffered, but their competitors would still have been operational. BlackBerry users would have been able to hold their service provider accountable, since they could switch to a competing platform. NTP once held its monopoly power over RIM's head, and now RIM holds it over us.

The Internet, with all of its flaws, is built on open standards. The protocols are well documented and not encumbered by patents. Any service provider is able to implement these protocols, and thereby (theoretically) interoperate with other service providers. For the most part, this works. And when it doesn't, we have the opportunity to hold our service providers accountable until they make it work. The Internet has no single point of failure (except perhaps DNS, but that's a discussion for another day).

When a service provider is given a legal monopoly over a protocol, the entire Internet suffers. Inventions and business processes can be patented, but protocols should be excluded. Until the law is changed, I encourage you to trade in your BlackBerry for a Windows Mobile, or some other device that can check email via the open protocols.