How do you handle a combined primary unique index with a sin

Hi Ruben

This sounds a bit odd to me. This suggests that two customers can have the same customernumber for a different dossier?

If you can't further normalize the database, I would take the easiest approach; number 1. :-)

Haha, I totally agree, but as you might have guessed it's not my database schema :)

Normalizing the database is definitely a good suggestion, but might be a stretch for the short term.

True, but that has two disadvantages:

• The ROWID does not mean anything to the user of the API
• The ROWID changes when performing a dump & load
  (so this potentially breaks integrations with 3rd party systems using the REST api)

I've learned the hard way that it's always best to have a primary key be a single field. Preferable a (long)int.

Imho the custnum within the dossier is only a counter and should not have any meaning except for ordering. Use a unique cust_num (id..)

I agree with [mention:ce4312d1e00542be918fe616c695a5de:e9ed411860ed4f2ba0265705b8793d05]. I would usually prefer to have a unique index on customer number if possible at all. In this case Solution 2 is simple to use.

But if normalisation in not possible at all then use Solution 1. The only thing I would like to mention is that do not use colon `:` as a delimiter. Colon `:` is a common character used in URLs. For clarity reasons I would use some other character like underscore `_` or hyphen `-`.

You can read more interesting stuff about URLs here, if you wish to.

In dynamics, we used a decimal identifier on all tables.

In smartcomponents, its a GUID if i recall correctly.

If you can't change the database (and depending on how you map out under the API) why can't you do /customers/{id}/{2ndid} ? You can then map each component of the URI to an index field.

Ruben,

You may also consider modifying the REST annotations in your application so that both input parameters can be interpreted separately by the method being called. I think this is possible, but it might depend on how you are creating your REST service. Look for "@progress.service.resourceMapping" on this page: knowledgebase.progress.com/.../How-does-Progress-interpret-the-annotations-created-in-the-Business-Entity-methods. My examples are based on building a class that inherits from the Business Entity class. If you have full control of the REST API (both server and client), you have options like the one below to work with. You may also look up the WEB transport which gives you a lot more flexibility over how your API URI structure works. I can't guarantee the code below works, but am including it in case it points you in a direction that might help.

Essentially, the class file would have something like this at the top.

@progress.service.resource FILE(name="Customer", URI="/Customer", schemaName="ds_customer", schemaFile="Apps/AppServer/customer.i").

Then in the read method, you would have something like this:

@progress.service.resourceMapping(type="REST", operation="read", URI="/~{custno~}/~{dossierno~}", alias="", mediaType="application/json").

   METHOD PUBLIC VOID ReadCustomer(

    INPUT custno AS CHARACTER,

               INPUT dossierno AS CHARACTER,

    OUTPUT DATASET ds_customer):

       DEFINE VARIABLE filter AS CHARACTER NO-UNDO.    

       /* code to build filter based on custno and dossierno. */

    SUPER:ReadData(filter).

   END METHOD.

OData uses one of 2 approaches - the one @hutct lays out (path segment) and one with a parenthesis-wrapping approach. See http://docs.oasis-open.org/odata/odata/v4.01/cs01/part2-url-conventions/odata-v4.01-cs01-part2-url-conventions.html#sec_KeyasSegmentConvention

The parens style would seem to me to be the better ("more correct") approach in the sense that path segments tend to be interpreted as parent-child although there's nothing in the spec that would indicate that they should be so <shrug>.  FWIW in PASOE webhandlers you _can_ define tokens to deal with the former (both actually) as below.

handler5=Example.API.OrderHandler: /OrderItems(OrderId={order-id},ItemNum={item-id})
handler6=Example.API.OrderHandler: /OrderItems/{order-id}/{item-id}

In your handler, you'd extract those values using the request

define variable orderId as integer no-undo.
define variable itemId as integer no-undo.
        
orderId = integer(poRequest:GetPathParameter('order-id')).
itemId  = integer(poRequest:GetPathParameter('item-id')).
        

I think I agree with the comments that an opaque id (like a ROWID or GUID or similar) isn't great , although that does veer into religious warfare so I'd just suggest not using a ROWID value as a ROWID.

[mention:bfaee6d85eff467b8f2498e8c27f30ae:e9ed411860ed4f2ba0265705b8793d05] : I am not even considering using REST annotations :).

Using the new WebHandlers like @Peter Judge (pjudge) mentioned, which offers me a lot more flexibility.

However, as it turns out, this customer told me that dossierno is apparently there 'company number' (their multi-tenant field in all their tables).

So with that information I turned everything around and start every REST URI as follows:

/api/dossier/<dossierno>/<entity>/<entityid>


Thanks all for the feedback!