When designing a RESTful API, should your URI map to tables (for the most part). I have 2 tables that look like this:


|id|first_name|last_name|email|role |
|1 |Jonny     |Walker   |a.com|User |
|2 |Jim       |Beam     |b.com|User |
|3 |Jack      |Daniels  |c.com|Admin|


|user_id|date      |status   |
|1      |2017-06-20|Available|
|1      |2017-06-22|Available|
|1      |2017-06-23|Available|
|2      |2017-06-21|Available|
|3      |2017-06-19|Available|
|3      |2017-06-24|Available|

In this case is it better to design the URI like:




When you read it the top makes most sense ("user 3's availability). But it seems that the bottom example would be a better way to separate the availability calendar data from the user data (i.e. first name, last name, email, role).

So my question is should the URI approximately map to the tables like the second URI example?

  • 1
    None of those. This question has nothing to do with the RESTful architecture style – K. Alan Bates Jul 7 '17 at 17:23

When designing a RESTful API, should your URI map to tables (for the most part).

No, absolutely not.

URIs do NOT map onto domain objects - that violates encapsulation. Work (ex: issuing commands to the domain model) is a side effect of managing resources. In other words, the resources are part of the anti-corruption layer. You should expect to have many many more resources in your integration domain than you do business objects in your business domain. -- Jim Webber

| improve this answer | |

The id that follows a collection should correspond to a unique resource of that collection. As a developer seeing


would be confusing since I would think that this is the availability resource of id 3. It would be better to write it in the form of


as a filter on the availability collection.

As for which to pick I think both are valid, and you can even support both with a single function in the backend since they are both the same filter.

| improve this answer | |

When designing a RESTful API, should your URI map to tables (for the most part)?

No. In a RESTful API, your URIs should map to (logical) resources.
It can happen that a resource in your application corresponds to (a row in) a single table, but that certainly is not the case when relations and/or more complex resources come into play.

For your example of users and availability, there are two commonly used options (which are not mutually exclusive. You can use both at the same time):

  • Make the availability information part of the user resource

    GET /users/3
      first_name: "Jack",
      last_name: "Daniels",
      email: "c.com",
      role: "Admin",
      availability: [
        { date: "2017-06-19", status: "Available" },
        { date: "2017-06-24", status: "Available" }
      links: { 
        self: /users/3
  • Expose the availability information as a sub-resource under the user resource

    GET /users/3/availability
      { date: "2017-06-19", status: "Available" },
      { date: "2017-06-24", status: "Available" }
| improve this answer | |
  • Implementation-wise, is it possible to build that only using SQL? Every solution I've come up with involves me performing a query on the users table and a query on the availability table and then merging the arrays in PHP. I feel like there should be something in SQL to do this without repeating the user data. – keelerjr12 Jul 8 '17 at 11:59
  • @keelerjr12: I am not an SQL expert and I am not aware of any mechanism in SQL to turn a set of rows into multiple columns. I would probably implement it as two queries and combine the results in the backend code. – Bart van Ingen Schenau Jul 8 '17 at 13:09

In addition to the @VoiceOfUnreason' answer.

REST APIs are meant to be one more layer of the application, not the application itself.

It's an interface for integrations. As interface you want it to be as decoupled as possible, so if the domain changes, the API not necessarily have to change too. In consequence, consumers don't have to be worried about these changes.

If the REST API mimics the domain model at such so low level as data base tables relationships, when changes happens, everyone suffers the consequences. Once the API is released, changing its interface is one of the worse thing to deal with.

That's why, in general, it's good to think in REST APIs as yet another abstraction in the application. So, don't (if possible) expose your implementation details to the world. Rather, do expose representations.

When modelling the URI templates, look for the URI that better informs to the developer what he/she might expect after the requests. URIs don't need to be human readable, but when they are, it makes the developer' job a bit easier.

I say developer, because from the client point of view (app client) URIs are meaningless.

| improve this answer | |
  • So what would be a better way to implement? Have a main .php where there is one rewrite rule that goes to that and then this parses it and routes it to the correct function? Or use different folders where the rewrite rule goes to the main .php file in that folder based on the URI and then that executes the correct function. To be it seems just one main.php in the main folder that would parse everything so you don't have a bunch of different files parsing URIs – keelerjr12 Jul 7 '17 at 17:09
  • 1
    It's ok having different files mapping URIs to functions. The important things is to keep the cohesion. Put together what should be together. For instance, URIs related with users go in one mapping file. Traverse URIs like /users/id/availability also belongs to the user mapping file. However, /availability/id is mapped in some other file. You might think otherwise, but despite the number of the files, having the responsabilities separated will make the maintenance much easier. – Laiv Jul 7 '17 at 17:19
  • you are forgetting the databases have this "modern technology" called views which is a way of abstracting the underlying tables (data model) – Ruslan Talpa Jul 7 '17 at 18:35
  • Indeed, views are useful, but I would not abuse of them or you might end up with two data models to maintain. Plus changes on the domain data tables may cause views to change too and hence the API. So no, mapping views is neither the way to go (IMO). – Laiv Jul 7 '17 at 18:48

Have your tables in separate schema, call it data, create another schema called api and place there only views that in the beginning are only mirrors of your tables. map your api to those views. now you have your tables decoupled from your api, you can independently change one without affecting the other.

also check out PostgREST the type of api you are building can be automated, you don't need to do it by hand

| improve this answer | |
  • I encourage you to review the answer. I don't think you have completely forseen the implications of mapping views as a way to decouple the API from the domian. – Laiv Jul 7 '17 at 18:50
  • please explain, can you give an example of such an implication? – Ruslan Talpa Jul 7 '17 at 18:52
  • Modifying any of the tables involved in any of the views. – Laiv Jul 7 '17 at 18:53
  • you modify the tables, then you modify the views in order for their output to be the same as with old version of the tables, exactly the same as you would change your code to work the same (expose the same api) with the new tables – Ruslan Talpa Jul 7 '17 at 18:55
  • Change the view to keep them backforward compatibility... What if you can't? What if you have versions? Trying to decouple the API from the implementation details with more implementation details is a solution fated to fail. If it doesn't, it's likely probable that at some point you will have to explain why your database has so many schemas and why the application and the API is so expensive to maintain. The flexibility required to absorb change will come from the code, not from the DataBase. Views will be useful for a little while. – Laiv Jul 7 '17 at 19:05

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.