SQL - REST - Storing data and retrieving it using friendly IDs

Question

UPDATE 09/06/2018

My models looks like this:

public class Activity
{
    public int Id { get; set; }

    public List<Additive> Additives { get; set; }
}

public class Additive
{
    public int Id { get; set; }
    public int ActivityId { get; set; }

    public Activity Activity { get; set; }
}

Original Question

I'm developing a RESTful API and i want to do it the cleanest way possible.

Currently i'm working on data retrieving. So i got this

If i create a new resource called activity and it has an array of objects, lets say (hardcoded, not representative):

{  
"name": "act1",  
"objects": [  
    { "obj1":"val1" },  
    { "obj2":"val2" }]  
}

I'll could add or delete objects from my array.
Now, my question is, if i want to access them with an url like this:

 Api/activity/1/objects/2

Which is the correct way to tell MySQL that this object is my 2th object of the list?

Or maybe i should select ALL objects of Act1 on the backend and filter them to retrieve just the 2th one.

Please add sample data for table structure – mtr.web Jun 08 '18 at 20:08 — mtr.web, Jun 08 '18 at 20:08

score 1 · Answer 1 · answered Jun 09 '18 at 22:32

1

It's pretty important that resources in your REST service have stable urls. So to figure out what 'second' is, you really need to think about what it means to be second in the list.

Are you ordering by something? If so, it's probably a bad idea to use this structure because adding a new entry can change the order and what was 'second' before might no longer be in the future.

The best thing you can do is add some kind of new id for these entries so accessing a specific resource by uri will always return that resource (or 404, etc).

answered Jun 09 '18 at 22:32

Evert

93,428
18
118
189

You mean access to resources by specific id. Then there would be no difference between `api/activity/1/additive/1` and `api/additive/1`, right? but using `api/activity/1/additive` would return the list of additives related to the activity with their respective ids – Jose Belisario Jun 10 '18 at 15:32
1

There is a difference if you want it to be. As a human, I can see `/activity/1/additive/1` belongs to `/activity/1` and I can manually manipulate it to get more information. I think this is a nice benefit. – Evert Jun 10 '18 at 20:28

score 0 · Accepted Answer · answered Jul 17 '18 at 17:24

I finally found the answer i were looking for. mysql two column primary key with auto-increment

This is a MyISAM engine feature.

For MyISAM and BDB tables you can specify AUTO_INCREMENT on a secondary column in a multiple-column index. In this case, the generated value for the AUTO_INCREMENT column is calculated as MAX(auto_increment_column) + 1 WHERE prefix=given-prefix. This is useful when you want to put data into ordered groups.

In my case, this query does the job

CREATE TABLE additive (
    id INT NOT NULL AUTO_INCREMENT,
    activity_id INT NOT NULL,
    other_column VARCHAR(30) NOT NULL,
    PRIMARY KEY (activity_id, id)
) ENGINE=MyISAM;

SQL - REST - Storing data and retrieving it using friendly IDs

2 Answers2