Database and Model Naming Conventions, the NULL Value

When creating a new database model, I always prefer to use the singular form when naming entities. For example, instead of using „customers“, I like using „customer“. It is in my opinion consistent with OOP programming.

When dealing with database tables, as beginners we think about them in the first way it comes to mind: as a table drawn on paper, or maybe as a HTML table. The table we first imagine is likely to have a header (the column names) and rows (each one for every entry in the table). However, there is a different way of looking at them. Each table could be seen as an Entity. An Entity represents a template, such as a „customer“, just like a Class is a template for an instance. It makes then sense to use the singular name when creating the table in this case.

This is because, when creating classes, it's much more intuitive to create a single customer with:

$object = new Customer();

rather than with

$object = new Customers();

Continuing this train of thought, the table columns are no longer columns, but are properties of the object. A database table has columns just like a class has properties. The semantics of NULL value for properties of a record: the object doesn't have that property. So, when we declare a field as Nullable, we are actually saying that a record for that entity can be instanced even without a value for that property. We could retrieve a nonexistent property from a record, and that will also return NULL. So, for a certain record, a non existent field and a defined one (but NULL one) should be identical from the API point of view. However, when we do in MySQL something like:

SELECT `inexistentColumnName` FROM `customers`;

we get the following error:

#1054 - Unknown column 'inexistentColumnName' in 'field list'

and there is no setting to control whether we can accept those columns or not. But because we still want to see the NULL values at work, we can create a test database with a couple of tables, after which we will use the LEFT JOIN query:

CREATE TABLE IF NOT EXISTS `a` (
  `idA` int(11) NOT NULL,
  `fieldA` int(11) NOT NULL
);
INSERT INTO `a` (`idA`, `fieldA`) VALUES (1, 111), (2, 222);

CREATE TABLE IF NOT EXISTS `b` (
  `idB` int(11) NOT NULL,
  `bField` int(11) NOT NULL
);
INSERT INTO `b` (`idB`, `bField`) VALUES (1, 555);

Having created these data structures, we now just query the a table using a LEFT JOIN with the b table. What this translates into is „Show me all the records from a that have an idA value that is found in b.idB“. The „LEFT“ actually says „And also return the records from a that have no match in b“:

SELECT * FROM `a`
LEFT JOIN `b` ON `a`.`idA` = `b`.`idB`;

The query returns:

idA	fieldA	idB	bField
1	111	1	555
2	222	NULL	NULL

So, now we should have our results, observe the NULL values, which in this context could be said to mean „This resulting record doesn't have the properties idB, bField“. After seeing how database tables and classes in OOP are analogous, it should be even more clear that we should name our classes and database tables in the same way. As you work with legacy applications and with other programmers, you learn to accept the plural as a different naming convention, which is okay as long as there is consistency. A few problems may arise only when having to deal with a mix of plural and singular names.

POST#0072 2009-APR-22

Help improve the Fusion Blog - express your opinion about the content on this page: I like it Can be improved

Commentary (3):

COMM#12622 2009-MAY-6

Alex wrote:

I feel aimed as „other programmer“ who mixes singular in plural names. And I must admit that sometimes my use of naming in code in general was a bit messy. It's good to have coding standards especially when working in teams. Though when working alone, it might help some unstandardized code as a creativity stimulus :) The table – class analogy I think is only partial correct. The relational databases have their own logic and optimisation. To construct and to use them only from an OOP perspective might cause serious problems and in some cases might be impossible to handle this way. Although OOP itself is not always the solution. We have to keep in mind the image of the computer as a mill of bits with a simplistic mechanism. OOP sometimes tends to get too abstract and far from the metal.

COMM#13288 2009-NOV-20

tadd wrote:

Ok, thanks a lot for your post. It was of good help to me, hope to hear from you soon again.

Have a look at my project rapidshare SE . Will be glad if it is of any use to you ;)

COMM#13534 2010-FEB-24

Volker wrote:

Google gerade im Web und bin auf dieser schönen und informativen Homepage gelandet. Bevor ich weiter surfe, möchte ich noch einen Gruß von der wunderschönen Insel Sylt hinterlassen.