Tuesday 15 July 2008 — This is nearly 15 years old. Be careful.
Jeff Atwood has a good post about database design: Maybe Normalizing Isn’t Normal, all about data normalization. I like the quip it ends with: normalize until it hurts, denormalize until it works.
In the comments, a few people quibble with the naming of his tables and columns. It’s an age-old debate: are relational database tables named with a singular or a plural? The original proponents of relational design used singular nouns: User, Employee, Manager. But conceptually, a table is a set of things, and so should be plural, no?
On the plus side for singulars, it works better in today’s ORM-heavy world. It simplifies the transition from objects to tables, since the class name will be singular. Typically, database table names have to be explicitly specified to make them plural since pluralization is hard to do automatically. At Tabblo, we have a table correctly named stories, but also one incorrectly named addresss.
Another minor point for the singular camp is the case where a single row in the table is actually described by a plural already. Suppose you have a class called UserFlags. What’s the table called? If you favor plural table names you’d have to pluralize it again to UserFlagses?
An advantage for plurals is that it makes SQL queries sound right. The statement “select * from users” simply sounds right. Although, if you have to qualify column names, it sounds odd again:
select users.name from users where blahblah
I suspect which side you lean toward will depend on how you were raised, like any religious argument.
On another point, Jeff shows a User table with a user_id column as the primary key. At first I recoiled: shouldn’t the primary key be named “id”? But as it happens, I have often made the mistake when typing ad-hoc SQL queries of using user_id in the User table, simply because I’ve been using it everywhere else.
If I type
select * from stories where user_id = 6
to see user 6’s stories, then it’s natural to type
select * from users where user_id = 6
to see user 6 himself. Of course it’s natural to use a table-qualified id column name for foreign keys, but for a primary key? I’ve never tried it, so I don’t know what pain I might incur further down the road.
BTW: Joel’s forum has an an old thread on the issue.
Foreign keys get a double prefix, so the customer table's foreign key reference to a user would be cstUsrID. It works for me and it's relatively easy to explain, which is all that really matters.
P.S. Some call it best practice to explicitly name your table with every query, so your last query becomes "select * from users where users.id = 6"
I don't know why, but this is hilarious to me! Great post as usual. Another thing in the endless list of things for programmers and dbas to argue about.
Then it's never in question: the name of the *type* is singular, hence the name of the class or table is singular.
We use singular table naming except for certain conflicting cases ("user" is a built-in function name in Oracle and it won't let you name a table that, so we're stuck with "users" - strangely enough, Oracle does let you use "role" as a table name)
Add a comment: