Resource identifier mapped to Composite key entity

1

Hello

I´m trying to define a standard for my API. Every resource I have right now, comes from a SQL Server Database. And most of them, are tables with a composite primary key.
Every resource has a href property pointing to itself, but i also need to complete an Id property to be able to identify it.
As they have composite keys, I thougth of setting the id property with the json value of the key object, for example:

Lets say just a silly example just to be more clear of a city resource:

City Table: countryId, provinceId, zipcode, name. PK = countryId, provinceId, zipcode

City Resource:

{  
   "href":"some link over here..",
   "id":[  
      1,
      3,
      "2456ZC"
   ],
   "countryId":1,
   "provinceId":3,
   "zipCode":"2456ZC",
   "name":"Some City Name"
}

Then, when I get in the server this resource, I can use the id object to get the entity of the database by key.
Because the json Id property is pretty complex, I thought of serializing it in the server to json: [1,3,"2456ZC"]

And encode it with base64 url safe giving this result: WzEsMywiMjQ1NlpDIl0%3D

Final result:

{  
   "href":"https://api.com/cities/WzEsMywiMjQ1NlpDIl0%3D",
   "id":"WzEsMywiMjQ1NlpDIl0%3D",
   "countryId":1,
   "provinceId":3,
   "zipCode":"2456ZC",
   "name":"Some City Name"
}

Is there any drawbacks of this solution? I know that if I add or remove one field of the primary key fields in the table the Id would change, but this would not happen.

2

This looks like a question for stackoverflow.com. Your table should have an ID column, even if it isn’t the PK.

3

I’d recommend you to have unique ID for each row in database table. As @michaelhibay said it could be not Primary Key (but better to be primary). It will significantly improve performance, simplify your HTTP requests and database queries.

4

Yes, I know thats the ideal solution, specially when you are creating a new database and new tables. But I have a whole database with almost 300 tables with primary composite keys, creating a unique key for each of them is not practical. Also, it would not improve performance, as the query is performed using the primary key that comes in the json of the id property. Setting an Id as Primary key would improve database indexing, thats true. But when I am populating the resource and setting their not expanded related resources, I need to get this surrogate key (Id) in the fly because foreign keys are not made with this Id. Lets say, a customer has a related city. The foreign key between them is by the three columns I named before (countryId, provinceId, zipCode), meaning that Customer will have these three columns in the table with a FK to City table. When I have to populate the next JSON, I will have to execute a SELECT with those three fields in City to get the surrogate key Id, and that is not efficient:

GET customers/WzEsMywiMjQ1NlpDIl0%3D

{  
   "href":"customers/WzEsMywiMjQ1NlpDIl0%3D",
   "id":"iMjAIl0AjxH0Il0",
   "isExpanded":true,
   "name":"John",
   "lastName":"Doe",
   "city":{  
      "href":"cities/WzEsMywiMjQ1NlpDIl0%3D",
      "id":"WzEsMywiMjQ1NlpDIl0%3D",
      "isExpanded":"false"
   }
}

GET customers/WzEsMywiMjQ1NlpDIl0%3D?expand=City

{  
   "href":"customers/WzEsMywiMjQ1NlpDIl0%3D",
   "id":"iMjAIl0AjxH0Il0",
   "isExpanded":true,
   "name":"John",
   "lastName":"Doe",
   "city":{  
      "href":"cities/WzEsMywiMjQ1NlpDIl0%3D",
      "id":"WzEsMywiMjQ1NlpDIl0%3D",
      "isExpanded":true,
      "countryId":1,
      "provinceId":3,
      "zipCode":"2456ZC",
      "name":"Some City Name"
   }
}

What I´m trying to find out is if this solution, of serializing the primary key and coding it its a good practice to have a unique id. Suggestions are very welcome thought.

5

Have you considered to generate UUID? It wouldn’t be coupled with your composite key.

6

Defining a UUID, or a integer ID is the same. I still need to perfom a select in the table with the primary key to retrieve it.

7

What I am gathering from you is your database is not optimized and is a bottleneck to your process, and instead of correcting this issue, you would like to be able to customize the format to match your case.

I would advise using standards and standard practices instead of rolling your own procedures because they will be confusing to consumers of your service. You are also leaking an awful lot of your implementation details by trying to create this composite identifier, and putting a lot of the burden of maintaining this on the consumer.

It would be better to put a materialized view around your tables indexed on the new id column which you calculate based on the composite keys internally. I would not advise you share these internal details, nor require consumers to use them to interact with your service. If the view is too much complexity, you could always add a column and insert the values themselves, but this is something which should be resolved before the DTO level.

8

@michaelhibay does this suggestion not force one to make the “my resource is an object” fallacy?
What the database looks like, what the entity objects look like and what the API resources look like are different things surely?

That said, the API expects there to be a unique ID for a resource but that ID needs to come from somewhere. Grabbing it from a database ID is purely convenient. However, in some cases, a resource only exists in relation to other resources?

9

I like the solution to encode the composite key with base64 to not leak the details of how the composite key is generated.

In my DynamoDB I have combination of HASH and RANGE key and I don’t want the details to leak to client.

HASH_RANGE
microservice|name|key_2020-07-14T00:00:01 => bWljcm9zZXJ2aWNlfG5hbWV8a2V5XzIwMjAtMDctMTRUMDA6MDA6MDE=

the response then can be like this:

{
    "data": [
        {
            "id": "bWljcm9zZXJ2aWNlfG5hbWV8a2V5XzIwMjAtMDctMTRUMDA6MDA6MDE=",
            "type": "entry",
            "attributes": {
                "microservice": "microservice",
                "name": "name",
                "key": "key",
                "oldValue": "old value",
                "newValue": "new value",
                "isDelete": false,
                "created": "2020-07-14T00:00:01"
            }
        }
    ]
}