RESTful Service Design – How to Create an Ordered List Resource

data modelingdata structuresdatabase-designrest

I've run into this same problem over and over again and I haven't found a solution that I really felt was optimal.

Say in an app, you have an ordered list and you let the user change that order by drag and drop or something. You want the changes in the order to persist. How do you model that?

How should I design a restful service of an ordered list resource?

In particular, how should I design the list and item model of a restful resource? The most common design I've seen is the item entity having an order or position property. Another approach I've heard is a doubly-linked list on the items.

What's an approach that doesn't write too much to the database and is generally fast to update and read for clients? How should the endpoints be exposed as?

Best Answer

Representing an ordered list is one of the difficult problems with relational databases. Adding an position property to the list-membership relation is the most common way to do this, since you can easily retrieve the ordered list by adding ORDER BY position to your SQL query, and because you can easily insert items in the middle of the list by averaging the values of the previous and subsequent list member, assuming the position is a float rather than integer.

Using doubly linked lists should be avoided, since it's easy to accidentally make the links inconsistent, and end up with a cyclic graph or tree instead.

However, RESTful APIs do not suffer from the restrictions of relational databases. You can just do something that feels natural, rather than using a hack like a position property.

If you only have up to a few hundred elements in the list, just transfer the whole list in a request. Assuming we want to reorder [1, 2, 3, 4] where the list members are IDs, we could

POST /url/of/the/list
Content-type: application/json
...

[1, 2, 4, 3]

The backend may then translate this to whatever database technology you are using, but the API user does not have to consider these details.

If the list is large and items would usually be requested individually, you can allow an index in the url:

GET /page/7

If you're into HATEOAS, the response can include prev/next links to make navigation simple, if the resource would usually be consumed like that. However, this need not imply that your database also contains this doubly-linked list.

If the list is very large, you might want to expose ArrayList-like operations such as insert or push/append. I could imagine a call such as

POST /url/of/the/list?at=1357;mode=insert
...

description of the item to insert

If reordering is a common use case and the reordering should be committed immediately, then you could offer an appropriate endpoint in your API:

POST /url/of/the/list/reorder-item?from=783;to=1357

If the reordered list should be committed explicitly, it will be easier to transfer the new order as a JSON document, see above.

Now it's not quite true that you can view your API as completely separate from the database technology you are using. However, it is best to keep the external API as free as possible from implementation details. If any reordering touches about 30 rows just to update an integer order column, that's no big deal. Just do the simplest thing possible and always update the whole list. If your scale requires your database usage to be more sophisticated, prefer capturing this sophistication in the backend, where it's easier to maintain consistency.

Related Topic