Joe Celko's SQL Programming Style (The Morgan Kaufmann Series in Data Management Systems)

Author: Joe Celko
All Stack Overflow 12
This Year Stack Overflow 1


by Bill Karwin   2018-03-19

In his book "SQL Programming Style," Joe Celko suggests that a collection (e.g. a table) should be named in the plural, while a scalar data element (e.g. a column) should be named in the singular.

He cites ISO-11179-4 as a standard for metadata naming, which supports this guideline.

Let me argue why this makes sense.

  1. Table is a set. Every row in the table is an object (columns = fields). In programming, you name the collections using plural names (Arrays and collections should have plural names to indicate that they are collections of objects rather than single objects), e.g. args in Java program.
  2. Java arguments are args in plural exactly because they can hold 0, 1, and millions of arguments.
  3. You iterate them for every student in students, not for every s in student. You select a subset of students from a set of students. You do not select a subset of students from a student. That is conceptual reason for the proper naming.

Debunking the fallacies. The most popular answer says

Reason 1 (Concept). You can think of bag containing apples like "AppleBag", it doesn't matter if contains 0, 1 or a million apples, it is always the same bag. Tables are just that, containers, the table name must describe what it contains, not how much data it contains. Additionally, the plural concept is more about a spoken language one (actually to determine whether there is one or more), a table is not intended to be read by a human.

Lets convert AppleBag => BagOfApples. Now, the same "conceptual" argument says somehting opposite to itself, we see Apples and therefore answer must be plural!

This is because there is nothing conceptual in this ****. It fails even to reason about English language, the simple logic. In English, a BagOfApples != AnApple!

The argument "Bag contains 0,1, .. millions" of apples just proves that the collection must be called "Apples", likewise Java arguments or Somehow, the enemies of civilization manage to conclude that singular must be used here. The posts is just a folder. Why should it be plural?

Let's teach mr. Artuwood some logic: folder is a folder and must describe what it contains, not how much data it contains.

In fact, if you start thinking you realize that apple contains apple describes nonsense. The true concept is that Bag contains Apples. Either we name our container Bag(of specific kind) or think what it contains, the Apples (those dirty bastards tried to reduce that to the numbering problem. But we are after apples, not their number.). Yes, if we abstract the wrapper away, we realize that we are after Apples, what is contained in it. We fetch and iterate either a Bag or Apples, not apple.

Reason 2. (Convenience). it is easier come out with singular names, than with plural ones. Objects can have irregular plurals or not plural at all, but will always have a singular one (with few exceptions like News).

Customer Order User Status News

Which convenience is he talking about? Let's just point out that that plural come out simpler than singulars.

Reason 3. (Aesthetic and Order).

Aestetics is on our side. It is simple, as he did. We just claim that and that is all what is needed to turn the table.

Reason 4 (Simplicity). Put all together, Table Names, Primary Keys, Relationships, Entity Classes... is better to be aware of only one name (singular) instead of two (singular class, plural table, singular field, singular-plural master-detail...)

Am I alone seeing that for simplicity, everything must become singular? Yes, forget the plural using English language. It will make things simpler.

In fact, in many languages, sex is attached to all things, not only boys and girls. I have noticed that it simplifies the referencing. When I say "fox, dog and wolf" in Russian and then say "he", it unambiguously means that I am referencing the "wolf" because "fox" and "dog" are "she". Now you say that distinction that helps to reduce ambiguity creates it. How so?

Probably, the logic is "let's keep arbitrary constructs in our language and enforce the disorder by removing the rules which make sense". Yes, the proposal to use singular where logic demands plural while keeping to attach inappropriate attributes like sex to inappropriate items is a pursuit of nonsense in our crazy world.

As of

SELECT reads better than SELECT

You probably need to SELECT FROM students as student

Ok, here probably is the argument: How is table's alias is singular if table is plural? Ok, this makes some sense. But saying that column (object's attribute) is singular, therefore, the object collection must be singular as well, is nonsense.

Reason 5. (Globalization). The world is getting smaller, you may have a team of different nationalities, not everybody has English as native language. Would be easier for a non-native English language programmer to think of "Repository" than of "Repositories", or avoid them type "Statuses" instead of "Status". Having singular names can lead to less errors caused by typos, save time by avoid spending extra seconds to think "is it Child or Children?", hence improving productivity.

Why should other non-native speakers, like me, buy making nonsense "for simplicity and internationalization"? Why not improve our English and keep the logic consistent? It is much easier to understand the language in this case, after all.

Otherwise, we should also abandon the verbs, the articles, the adjectives and speak only in nouns, for simplicity. It would be simpler (and more productive) if we limit our speech to moooo and nothing else, for the animals to be able to speak English as well. This way we achieve much wider internationalization.

Reason 6. (Why not?). It can even save you writing time, save you disk space, and even make your computer keyboard lasts more!

This argument supports why we should abandon the plurals in our human communaction as well. No, instead of moooo, mo moo, moo moo, we will say I, III, III, II. Much shorter than with singular proposal.

The only meaningful argument for the singular is that table is a very special set of objects. Table is a Class! Yes, it contains all objects of specific type and we use singular for class names. Class Dog contains all dogs. What is dog1? It is a Dog. On the other hand, users deal with tables as collections. They add/remove items into collection and we use plural for collections.

by anonymous   2017-08-20

You could try checking out Joe Celko's book SQL Programming Style. I'm sure that there are a lot of people who disagree with his style, but it's a good start.

Some of my own "rules"

  • SQL keywords are always all upper-case
  • Table names are "proper" case, while columns and variables are all lower-case
  • Each "major" clause in a statement is at the start of a line
  • JOIN and WHERE criteria appear beneath and are indented and aligned
  • Nested items are indented further
  • I use aliases for all tables and views

For example:

        WHEN column_5 = 'Blah' THEN 1
        WHEN column_6 = 'Blah' THEN 2
        ELSE 3
    END AS column_alias
    My_Table MT
    MOT.column_1 = MT.column_1
    MT.column_2 = 'Some Value' AND
        MT.column_3 = 'Some other value' OR
        MT.column_4 = 'Some other value'
by Bill Karwin   2017-08-20

I wouldn't use a naming convention for purposes of alphabetizing table names. It's nice when it works out that way, but this shouldn't be by design.

Read Joe Celko's book "SQL Programming Style." His first chapter in that book is about naming conventions, guided by the ISO 11179 standard for metadata naming. One of his recommendations is to avoid unnecessary prefixes in your naming convention.