Oct 14

DB Table Naming Convention

Information 1 Comment »

It can be common for developers to be involved in the creation and design of databases for an application they are developing. Since most developers aren’t trained as Database Administrators, it is important that they develop some sound approaches to writing SQL and Data Definition Language (DDL). I’ve previously reviewed Database in Depth: Relational Theory for Practitioners by C. J. Date in the post, Book Review: Database in Depth. I highly recommend getting it as a guide to best practices for any relational database work.

Singular or Plural

One of the choices that invariably occurs is what to use for table names. Sometimes the particular RDMS may restrict the choices, but often this is pretty wide open. What I’m interested in is the small decision of whether to use the singular or plural form of a noun for the name. A common example is the table where user data is held. Should the table be named user or users?

My Choice

I am not sure there is a standard answer. This has been discussed before (for example, see Database table naming conventions), but I haven’t seen anyone justify why it is better to make the choice for theoretical reasons. Some frameworks or DDL tools will make the choice for you as I discuss below for Ruby on Rails. My own choice has been to use the singular name. I think this leads to improved code quality because:

  1. It leads to consistency in Foreign key names (order.user_ID references user.ID).
  2. It makes ORM a 1:1 match (the user object reflects a row in the user table).
  3. It avoids some of the annoyance of the English language pluralization rules (see Rails below).
  4. Then there are small things like ordering of tables. For example, user, users, userOrder, and user_line_order may not sort in the most effective way.
  5. I believe it is the simplest design that works. Using the plural form makes me think there is a singular table from which the plural form is distinguished. Would that be a table which is guaranteed to only have a single row?

Rails makes a default choice?

So what happens when you use the command script/generator scaffold user in a Rails project. Rails generates a DB migration for creating a users table. There are ways to control the model names and DB migration, but it is interesting that the developers of Rails decided to make the default choice of using plural table names. (See Agile Web Development with Rails for example syntax on how to not use defaults).

But what about that tricky English language? Well, script/generate scaffold person produces people. Pretty cool. And woman produces women. But then goose becomes gooses. Woops! Now you may have no need for a goose object in your application, but I am sure there are other examples like this. Alas, this appears to be a bug in the Rails framework, but just think how easy it is for you to bump into this yourself when trying to remember the plural form of the object you are working with. In addition, these names show up in the URL for Rails routing. Thus they can become public. I’d rather see geese in applications I work on.

I realize there are pros and cons to the choice of using singular or plural names. In terms of producing quality code, what do you use and why?

Tagged with: • • •
Aug 21

Accidental Perceived Functionality

Information No Comments »

Recently I learned of an issue with an application where presentation of a resultset in the UI led end users to assume the order of the resultset was a feature of the application. This is a pretty common scenario that I have seen with many applications both on the web and off. Usually it is the result of not using an ORDER BY clause in the SQL query or not having any logical column to sort by in the resultset. The problem is amplified in the case where the end user is the one who created the data in the first place and believes that the order they created the data (via the GUI normally) is perserved by the application. In time, the resultset from the scenario described above may be returned in a different order. Consult your RDMS guru for all the scenarios where this might happen. I have seen it occur during import/exports of data, database replication, and during updates to tables where all rows are affected.

Regardless of the cause, the net result is guaranteed to be user complaints about an issue with functionality (ordering of the resultset in the UI) which was never intended to be a function of the application. This is what I term accidental perceived functionality (no doubt, somebody has a better name). Please post other examples of this (so we can all learn) in the comments.

Tips to avoid this situation:

  1. Hire a good business analyst to capture all requirements (including eliminating possible points of confusion). This won’t necessary avoid the problem, but will increase the odds.
  2. Make it a habit to always include an ORDER BY clause in all SELECT statements.
  3. During table definitions always provide a column that allows ordering of results in accordance with the desired functionality (capture by requirements)
  4. Thoroughly unit test the results sets (including order) of your DAOs.

Tagged with: • •
Jul 12

Book Review: Database In Depth

Book No Comments »

Today I am continuing a series of posts in which I will be reviewing some of the books which are related to the development of quality software. They may be specific to a certain technology or a software development methodology.



I’ve chosen to continue this series with Database in Depth: Relational Theory for Practitioners (amazon affiliate link) by C. J. Date. The target audience of this book is experienced database practitioners, not novices. If you’ve been involved with writing lots of SQL and DDL, then I believe the book is aimed at you. It explains the basic principles of relational theory in depth. The book is completely independent of any particular products and the SQL standard. One of the fascinating aspects of the book is the revelation of how few (if any) commercial database products truly implement the relational theory.

The author C. J. Date, worked closely with the E.F. Codd (inventor of the relational model), strongly influenced the development of database technology, and is considered to be a master in the field. For such an theoretical book, I found his writing style to be very lucid.

Theory discussed includes relations, types, tuples, relational variables, relational algebra and database design theory. One of the most applicable sections is the discussion of normalization. Another principle that I have taken to heart is the prohibitions of nulls. What is a method of implementing this principle? Well here is one. First suppose you have the following situation:

User
id username hair
1 user1 black
2 user2 NULL

To avoid the NULL in the table, you can design the schema to be like the following:

Table 1:

User
id username
1 user1
2 user2

Table 2:

User Hair Color
user_id hair
1 black

By breaking up the original table into two tables we have avoided the use of NULLS. Complimenting this approach to database design is the use of OUTER JOINs in SQL.

If you want to become a better developer, I recommend reading Database in Depth: Relational Theory for Practitioners (amazon affiliate link).

Tagged with: • •
preload preload preload