Is it good/bad design to join composite key with underscore in url?

Question

I'm looking for the best practices on RESTful API design for the following use case:

Table1     Table2
Id1        Id1
Id2        Id2
Id3        Id3
Name       Name
           Table1Id1(FK to Table1)
           Table1Id1(FK to Table1)
           Table1Id1(FK to Table1)

Suppose i have endpoints like below for Table1:

/root/table1 (to get list of records)
/root/table2 (to get single record by primary key)

Now here my question is which would be the best way from below two to represent composite key in second url :

/root/e1/Id1/Id2/Id3

or 

/root/e1?Id1=1&Id2=2&Id3=3

Suppose i have endpoints like below for Table2:

/root/table1/Table1Id1_Table1Id2_Table1Id1/table2 (to get list of records for table2 by table1).

Now here is my question that is above url valid and appropriate in case of composite key?

Any advice on a good pattern to follow for this use case would be greatly appreciated.

This: `/root/e1/Id1/Id2/Id3` is really good for SEO's (see stackoverflow url pattern ;-) Since you are only using Id's, just go for the parameter pattern: `/root/e1?Id1=1&Id2=2&Id3=3` You could also wrap it in a json object and send that as parameter. — Stefan, Aug 05 '17 at 14:35

VoiceOfUnreason · Accepted Answer · 2017-08-15T02:03:38.610

Any advice on a good pattern to follow for this use case would be greatly appreciated.

Don't couple your resource identifiers to your (current) database schema; that violates encapsulation.

I'm looking for the best practices on RESTful API design for the following use case

REST really doesn't care. As far as REST is concerned, the URI is opaque; any information encoded into it is done at the server's discretion and for its own use.

The relevant concerns are RFC 3986, and your local design conventions.

The path component contains data, usually organized in hierarchical form, that, along with data in the non-hierarchical query component (Section 3.4), serves to identify a resource within the scope of the URI's scheme and naming authority (if any).

Path elements are supposed to be for hierarchical data -- think about the way that relative URIs resolve.

Based on your description here, I wouldn't think that the foreign keys have a natural hierarchy to them; certainly not in the general case. So using the non hierarchical part of the URI (the query) might make more sense.

Another possibility to consider would be matrix parameters; you can combine the foreign keys into a single path segment, thereby avoiding any suggestion of hierarchy among them.

score 5 · Answer 2 · answered Aug 09 '17 at 05:51

Agree with VoiceOfUnreason

Don't couple your resource identifiers to your (current) database schema; that violates encapsulation.

Dont do that

For your specific usecase

As a general best practice, REST Urls should follow a predictable and hierarchical pattern to identofy resources. This brings much transparency between Client and Server. So suppose you have parent-child relations ship in your entity it should be better be designed as

/{appname}/{version}/parent/{parentId}/child/{childId}

instead of just having

/{appname}/{version}/child/{childId}

In your usecase the non-hierarchical part of URL is Id1/Id2/Id3

Best approach to have a unique identifier for your tables. But if it really can not be done then you should rather go for

/root/e1?Id1=1&Id2=2&Id3=3

This gives a notion "get me the e1 with is having Id "1" and Id "2" and Id "3" which is correct in your context

/root/e1/Id1/Id2/Id3

This is non standard and therefore should be avoided

to get list of records for table2 by table1

Your should have

/root/table1/table2?Table1Id1&Table1Id2&Table1Id1

Upvoted for the answer and for your kind efforts and spending your valuable time on this answer but dont you think when i will have deeper level of entities then url will become nasty and long — I Love Stackoverflow, Aug 09 '17 at 05:57
yes thats why the entity design is so important. Your API design could cover only so much. Two tables with three composite keys each with composite foreign keys is a complex scenario. So even though URL might become long it certainly wont become nasty and would be easy to interpret. Which one do you think purely from readability perspective is better? /root/table1/table2?Table1Id1&Table1Id2&Table1Id1? or /root/table1/Table1Id1_Table1Id2_Table1Id1/table2? — Abhijit Mazumder, Aug 09 '17 at 07:58

score 4 · Answer 3 · answered Aug 14 '17 at 21:36

The best REST API design guide I've seen by far is Google's one: https://cloud.google.com/apis/design/

On the topic of your question it contains these 3 sections:

Resource Oriented Design: Talks about a general way to think about your endpoints.
Resource Names: Describes on good approaches to name your resources.
Standard Methods: Describes when to use GET vs. POST and the other HTTP methods.

It's a really good read which I highly recommend.

Answering your question based on the Google guide I would say: use /root/e1/Id1/Id2/Id3 rather than the other version which should be used to create new records.

But probably you shouldn't be exposing your DB schema as the other answers recommend. That's why I mentioned the "Resource Oriented Design" section above. You probably want some sort of abstraction on top of your tables. Unless your operation are explicit operations over the DB schema itself.

Yes, my case are explicit operations over the DB itself. The DB uses an Id and a partitionKey to identify a resource. I'm currently doing something like grandparent/parent/parentId/child/childId — user33276346, Jan 05 '21 at 01:55

Is it good/bad design to join composite key with underscore in url?

3 Answers3

Linked