rest: modify the order of the resources associated to a resource

Question

I'm trying to write a REST interface for an application.

The Model

In the model of this application I already have a situation like the following:

• A person can have multiple activities.
• An activity can be referred by several people.
• The activities of the people are ordered with a specific field (position).

I've represented this situation in my db with three tables: people, activities, people_activities. The table people_activities features three fields: person_id, activity_id, position.

The new Requirement

Everything worked fine until now but now I've got a new requirement, I should be able to insert an activity for a person on top of the others.

So if the content of the people_activities table is for instance this one:

"mark reading 0" "mark running 1"

if I received the request to add "mark juggling" the result should be:

"mark juggling 0" "mark reading 1" "mark running 2"

The solutions

The dilemma for the REST interface now comes from the fact that I identify only two options:

• using a single URL like /people/mark/addActivity but this doesn't seem restful at all since it modifies resources not referred by the URL and contains a "verb" too much.
• using some URLs like what I'm already using (something like /people/mark/activities or /people_activities?person=mark) and post the new change towards all these resources. This seems to be restful but very sloppy in my opinion.

What's the proper way to deal with this situation in your opinion? Is there a third option I'm not considering?

First Edit

Just by thinking better about the questions I'm realizing that another reasonable solution would be to end with this situation in the database:

"mark juggling -1" "mark reading 0" "mark running 1"

Because the position is just a number that has not a "real" value for me. In practice I cannot do that but it looked like a precious information and a way to add a new resource without modifying the other associations, that is not something i need to do from my business logic point of view.

Maybe another error is that I'm letting to much database data spilling on my interface. In this case what i really need from a business perspective is the order of the elements, not their position. The position thing is just a technical detail I have used in the database to accomplish the ordering.

So another question maybe:

• is reasonable in your opinion to modify some information in your database if this information is just a technical detail and it's not exposed to the interface users?

Answer 1

I think your solution of inserting the new entity with a position of min(position) - 1 sounds good.

We are also looking to build a RESTful API backed by a SQL database that allows the resources to be reordered and have faced this problem with the implementation details of how to add a new entry to the start or in the middle of the list without having to update the positions in all the entities.

In our implementation we plan use a floating point number for the position field and plan to follow these rules:

• If the user wants to insert the entity to the start of the list, insert with a position of min(position) - 1.0
• If the user wants to insert the entity to the end of the list, insert with a position of max(position) + 1.0
• If the user wants to insert the entity anywhere else, insert with a position equal to the average of the positions of the two entities either side.

With the technology we are using we get 1073 inserts between two entities before we need to rebalance, by updating the position fields in all the entities to be all spaced 1.0 apart from each other. This is fine for our use case.

Side note: With a graph database you don't have this problem. It is very easy to add a new entity anywhere within the list by just updating the two relationships of the nodes either side, just like when you insert something into a Linked List. So that is something else to consider if you are not tied to a SQL database

All of the above is implementation details. With regards to how the API should look to an end user, I would vote to make it appear however is easiest for your clients. In our use case I hope for us to make the position field appear as a 0 based integer index (just like an array), as that will be easiest for our clients. This would mean that inserting a new entity at the beginning of the list will make it appear that the position field in all the other entities has changed, but I think that is fine. It doesn't fly in the face of the REST philosophy too much. It's pragmatic.