Update Rule - Table Names

[RichardSoule]

First, let me say, these are by far the best set of SQL and PL/SQL coding rules out there. Just about everything in them is great. I did have this one very strong quibble... Let me tell you, once it is adopted people just love it. So much less work and less thinking about things that don't matter.

Suggestion:

Table

Singular name of what is contained in the table.

There are many reasons why this is far better than plural names.

1) In general, tables represent entities. Entities are singular. This encourages the (lost?) art of Entity-Relationship modeling. 2) If all table names are singular, then you don't have to know if a table has a single row or multiple rows before you use it. 3) What is the plural of news? lotus? knife? cactus? nucleus? There are so many words that are difficult and nonstandard to pluralize that it can add significant work to a project to 'figure out the plurals'. 4) For non-native speakers of whatever language is being used for table names, point number 3 is magnified signicantly. 5) Plurals add extra unnecessary length to table names. 6) Bar far the biggest reason: There is no value in going through all the work to plural a table name. SQL statements often deal with a single row from a table with multiple rows, so you can't make the argument that employees is better than employee 'because the SQL will read better'.

Add a comment to the database dictionary for every table and every column in the table.

Optionally prefixed by a project abbreviation, but strongly consider schema separation for different projects.

---

Previously, the rule was:

Plural name of what is contained in the table (unless the table is designed to always hold one row only – then you should use a singular name)

Add a comment to the database dictionary for every table and every column in the table.

Optionally prefixed by a project abbreviation.

[PhilippSalvisberg]

Trivadis has a history of using Oracle's CASE*Method and CASE*Tools. Back then every entity was entered into the system with its singular and its plural name. The plural name was generated by the system by a simple algorithm (adding s to the singular name). This was wrong in most cases, since back then it was not uncommon to use entities in our mother tongue German. We were used to have these plural values available in our system. Even today they are (sometimes) available in SQL Developer Data Modeler in the field Preferred Abbreviation.

The singular name of an entity was used in all diagrams. The singular and plural names were used to build sentences based on the relationship name and the cardinalities. Something like that

• every department may have one ore more employees
• every employee must be assigned to one and only one department

When transforming the a logical model (entities) to a relational model (tables) the plural name of an entity was used for the table name by default. The default made sense back then (e.g. in a query such as SELECT first_name, last_name FROM employees).

There are controversial discussion about using singular or plural names for tables. We have this discussion at the beginning of every new project. For me it is important that a consistent strategy is applied. This means. Either plural or singular. And since we often are extending an existing model we should simply stick to the principles chosen earlier.

Maybe we should change the rule in this direction.

[RichardSoule]

I get it. History is important. I loved CASE. SDDM isn't quite the same...

Amazingly I get some projects that are total green field opportunities, and for those, singular.

For existing stuff, that is often a mixture of singular and plural (let's face it, so many systems were built without any standards), anything new is singular.

If there ever existed a perfect 'plural' system, then sticking with plurals would be fine.

I view these naming "rules" as, "In the absence of anything, you should probably do this."

[PhilippSalvisberg]

Just to be clear. I still favour plural names for tables and views in (new) models. 😉

I like the chapter Should Table Names be Plural? in SQL Naming Conventions by @pgulutzan:

Which is correct: Employee or Employees?

Koch and Loney represent the tables-are-singular argument best. They give examples like address book and phone book and car club and restaurant list — notice that these are all singular. Very fairly, they give some counter-examples too. But they find that the singular is more common in typical English. Besides, we're naming a set, and there's only one set.

I have also seen — in Usenet discussions — a suggestion that ISO 11179 says names should be singular. I think, however, that this is a misinterpretation of the standard.

And now the counterattack.

First, when we label a container, we label its contents. So although bean jar is a good English term, the name we paste on the jar says Beans. Second, the use of a singular can give the (presumably false) impression that the set contains only one row.

Looking at vendor manuals, I find that IBM and Microsoft appear to follow no fixed rule, but Oracle examples of table names are consistently plural. The tiebreaker is the ISO document 9075-11, which lists the views in the metadata schema INFORMATION_SCHEMA. These views mostly have plural names.

What do you write on the label of this jar:

"Beans" or "Bean"?

[RichardSoule]

I get all those arguments.

For me, it just boils down to this: Why do more work for no benefit? Once you use singular names, you never want to use plurals.

well well_bore well_bore_completion well_party

vs

wells well_bores well_bore_completions well_parties

[PhilippSalvisberg]

Why do more work for no benefit?

The time to write plurals instead of singulars is irrelevant.

Sometimes we do not clearly distinguish between a collection and a row. Here's a counterexample (using plural for the collection of rows and singular for the row):

   select department.department_name,
          employee.last_name,
          employee.first_name,
          employee.salary
     from employees employee
     join departments department
       on department.department_id = employee.department_id
 order by employee.salary desc

Once you use singular names, you never want to use plurals.

I've done both. More than once. Nonetheless, if I have a choice, I'm using plural names for tables and views

Hence, let's agree to disagree.

[RichardSoule]

The lable on the jar?

BEAN_JAR

But I'd probably shorten it to BEAN 😉

[RichardSoule]

For your counter example, during a code review, I'd strongly suggest to the coder that short table aliases are often clearer, and that they are more effecient for the database, and that "on" clause should be a "using" clause. 😁

I'm relatively sure that I can't win you over on this one.

I'm in the process of forking and updating your standards because they are so awesome. They are, by far, the best set of guidelines that I have seen. There are a few missing things (I'll be writing on EBR for example) and a few things that I just think could be just a bit better (singular table names, k instead of co in front of constants, all lower case code, etc.).

I'll ask... Should I put issues here when I make changes? Or just make changes in my fork?

[PhilippSalvisberg]

(...) short table aliases are (...) more effecient for the database (...)

Hmm. I hope you are joking.

I'm in the process of forking and updating your standards because they are so awesome. They are, by far, the best set of guidelines that I have seen.

Thanks. @rogertroller has done most of the work.

Should I put issues here when I make changes? Or just make changes in my fork?

It's by far simpler if you just change and extend your fork directly without keeping us in the loop. However, if you stumble over something which is wrong, then please let us know (e.g. via an issue for this repository). Thank you.

[RichardSoule]

Well, negligably more effecient... 😉

[RichardSoule]

And thanks, I'll do that.