Why does convention say DB table names should be singular but RESTful resources plural?

Question

It's a pretty established convention that database table names, in SQL at least, should be singular. SELECT * FROM user; See this question and discussion.

It's also a pretty established convention that RESTful API resource names should be plural. GET /users/123 and POST /users See this one.

In the simplest database-backed API, the name of the resource in the URL would be the table, and the data elements in the URL and request/response bodies would map directly to the columns in the DB. Conceptually, I don't see a difference between operating on the data through this theoretical API versus operating on it directly through SQL. And because of that, the difference in naming conventions between user and users doesn't make sense to me.

How can the difference in pluralization be justified when, conceptually, the REST API and the SQL are doing the same thing?

Answer 1

The REST spec (whatever level you want to go with) wasn't designed as database access. It is trying to bring standardization to API access. The SQL conventions mentioned (whether you want to use them or not) were not designed with API access in mind. They are for writing SQL queries.

So the issue to unpack here is the conceptual understanding that an API maps directly to the database. We can find this described as an anti-pattern at least as far back to 2009.

The principal reason this is bad? The code describing "how does this operation affect my data?" becomes client code.

This has some pretty terrible effects on the API. (not an exhaustive list)

It makes integrating with the API difficult

I imagine the steps to create a new user documented as something like this:

1. POST /users { .. }
2. POST /usersettings { .. } with some default values
3. POST /confirmemails { .. }

But how do you handle a failure of step #2? How many times is this same handling logic copy-pasta'd to other clients of your API?

These data operations are often easier to sequence on the server side, while being initiated from the client as a single operation. E.g. POST /newusersetup. DBAs may recognize this as a stored procedure, but the API operation may have effects beyond just the database.

Securing the API becomes a black hole of despair

Let's say you need to merge two user accounts.

1. GET /users/1
2. PUT /users/2 { .. }
3. DELETE /users/1

How are you going to setup a user permission to allow the merge feature while not allowing user deletion? Is deleting a user even fairly represented by DELETE /users/1 when /usersettings also exists?

API operations should be looked at as higher-(than-database)-level operations which may cause multiple changes in the system.

Maintenance becomes harder

... because your clients depend on your database structure.

Based on my experience with this scenario:

• You cannot rename or remove existing tables/columns. Even when they are named incorrectly for their function or are no longer used. Clients will break.
• New features can't change existing data structures, so its data and functionality is often artificially separated even when it holistically belongs with an existing feature.
• The code base gradually becomes harder to understand due to fragmentation, confusing names, and left-over baggage which can't be removed safely.
• All but trivial changes become increasingly risky and time-consuming.
• The system stagnates and is eventually replaced.

Don't expose your database structure directly to clients... especially clients you do not have developmental control over. Use an API to narrow the client down to just valid operations.

So if you are using an API as just an interface straight into a database, pluralization is the least of your worries. For other than a throw-away experiment, I would suggest spending some time determining the higher-level operations the API should represent. And when you look at it that way, there's no conflict between pluralized API entity names and singular SQL entity names. They are there for different reasons.

Answer 2

REST API and the SQL are NOT "doing the same thing"

The OP asks:

How can the difference in pluralization be justified when, conceptually, the REST API and the SQL are doing the same thing?

Ah, but grasshopper, it may appear that the RESTful interface and the SQL tables "are doing the same thing", but good programming hygiene tells us that there's always an intermediate layer that mediates between the REST API and database. To ignore this point is to stray from the path to software enlightenment! :)

So RESTful APIs and SQL tables can happily follow their own idiomatic naming conventions, which are well documented and thoroughly discussed elsewhere.

Answer 3

Be consistent.

A table can be conceptualized as a data structure or relationship, a type definition. It can also be considered a collection of objects. The former leads to singular names and the latter leads to plural names. It's only a headache if they are mixed.

For example:

create table person ( id int, given_name text, surname text )

The CREATE statement defines a data structure of which multiple instances can be created. This makes SQL more readable for WHERE clauses and joins, regardless of how many instances are being joined or filtered. Essentially, the queries in the set language SQL can be paraphrased as "server, for the objects of this type, do the following..."

Alternatively,

create table people ( id int, given_name text, surname text )

This creates a collection of people that effectively have the same, unnamed data structure. Conceptualized this way, queries can be paraphrased, "server, for objects stored in this location, do the following..."

My preference is for the former, because the fact that instances of the same type are stored in the same physical location is only a detail of how most servers are implemented. It is not even true if data are partitioned/sharded across tablespaces or server instances. But, I would not fight it if I walked into a system of the latter.

Oh, and fearless_fool, I don't know if any other systems have this, but Informix has a RESTful API directly to the database entities. While you are correct that is often better to put an abstraction layer between the UI and the storage layer, not all application designs require it. If the database is well normalized, various ways of filtering and combining the entities are really part of the View, and the DB stores the Model. As is often necessary, when requirements change, you can always create a VIEW in the DB to mimic the previous table structure. If this is difficult, you probably didn't normalize your database.

Also, in an object-relational DBMS, you can have type definitions independent of table definitions. In that case, there is less ambiguity about whether the definition is a type or a collection of instances, but most people don't define types separately from tables. When the CREATE TABLE carries both meanings, it's kind of a user-preference.

It looks like the majority of the DB domain decided to think of tables as representing a type of object and most of the REST domain decided to think of entities as being in collections. But, just like you should not be surprised to find snake_case in the DB and pascalCase in JSONs, you should not be surprised that different domains have different conventions.