47

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?

10
  • 3
    There's no single convention in DB table naming nor RESTful resource naming that everybody follows. On the contrary, there may be many conventions. It's not surprising that some may clash.
    – Eric King
    Jul 23, 2015 at 20:24
  • 20
    There is no such established convention. I've always used plural table names. users, accounts, etc, since they are holding more than one of that thing. Jul 23, 2015 at 20:43
  • 6
    @GrandmasterB The convention do exist and it has its origin in Codd's relational model where "relations" (that become tables, not to confund with relationships) have singular names because a relation is a list of things not several lists of things. Each relation is one list. Relations model domain entities. Entities names are singular in Codd's relational model. There's abundant literature about it as to say it doesn't exist. But it is perfectly OK for you to use plural names if you want. Jul 23, 2015 at 20:59
  • 4
    @user61852 There are people who may use it by convention. But it is by no means a broadly followed industry convention as presented in this question in the way, say, that camelCase or MarkDown is. Jul 23, 2015 at 21:08
  • 6
    Also keep in mind that REST is not a database access protocol. There is no reason that DB naming conventions (which ever one you go with) and URL naming conventions (which ever one you go with) should be anything alike, they have nothing do with each other. Two very different domains. It no more makes sense to ponder why databases use singular and URLs uses plural than to ponder why databases use single but my sys admin names his file system directories plural. Poorly designed web frameworks have people thinking REST is something to do with DB access, but it isn't. Aug 5, 2015 at 8:27

3 Answers 3

19

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.

4
  • 2
    Answers a different question. OP does not imply direct association of API and DB entities, just presence of some entities in both contexts.
    – Basilevs
    Feb 3, 2017 at 19:07
  • 2
    Feel free to post an answer to the question you think is being asked. Feb 3, 2017 at 22:18
  • 4
    @Basilevs Actually I think this does answer the question. Sometimes answers may appear indirect when a question is framed around incorrect assumptions. The "presence of some entities in both contexts" implies they are the same entities, which implies a 1 to 1 correspondence between some and not others. Such a correspondence of an API over a complex data model implies a flawed design. Feb 25, 2017 at 4:44
  • Among these are many reasons I stopped using Spring Data Rest. Jan 6, 2019 at 6:04
10

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.

2
  • I don't think OP implies that they are the same with that comparison. What I understand from the question is that REST API (as in the Representational State Transfer way, the architectural style), endpoints represent entities and actions are performed on it. SQL tables also can represent entities, then why the difference in criteria to represent them in singular or plural, and I see this a valid concerns for people that try to be consistent in their representations. This answer doesn't address that question.
    – Havok
    Sep 28, 2021 at 21:21
  • @Havok I hear your point, but you must not overlook the fact that REST and SQL tables are not "doing the same thing" as claimed in the OP, so there's no reason to expect they'd use the same terminology. Sep 28, 2021 at 22:57
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.