How to design for multiple 3rd party APIs

[JMW]

I find myself writing more and more code to interface with 3rd party websites (databases) via RESTful type APIs and importing and exporting data using XML and JSON.  It is a Filemaker application written for the health industry and contains patient information, medical records, insurance records and billing and accounting data.

Before I continue further down this road I am wondering if anyone has any ideas regarding whether I should create a single table with one record for each 3rd party application or a single table for each 3rd party application.  Or does it matter in the long run?  I can see that there are pros and cons to each.  The single table would require field names be quite generic and possibly not truly descriptive of the data they contain where as multiple tables with a single record would allow descriptive names but only contain a single record.  

Any insight would be appreciated.  Before working on this software I worked in the engineering world on computer aided design software.  I wrote lots of code but did not deal with databases.  Therefore, I do not have a deep understanding of the ramifications of a single table for multiple applications vs multiple tables for each application.

[comment]

16 minutes ago, JMW said:

whether I should create a single table with one record for each 3rd party application or a single table for each 3rd party application. 

What kind of information would be stored in this structure? 

[JMW]

Things that are required to interact with the 3rd party api.  For example, the website url, and any information that doesn't change that would be required.  I have a couple of items I can use as an example

curl -o apexEdiOutput.txt -X POST https://sandbox.services.apexedi.com/api/V3/claims/submit?vendorSiteId=<vendorSiteId> --header "Authorization: Basic <base 64 encoded keyword>" --header "Content-Type: application/json" -d "@<path and filename of json>"

https://rx7.drfirst.com/sso/portalServices?rcopia_portal_system_name=<vendorname>&rcopia_user_external_id=<external userid>&service=rcopia&action=login&rcopia_practice_user_name=<practice username>&startup_screen=patient&rcopia_patient_id=<patientnumber>&close_window=n&allow_popup_screens=n&limp_mode=n&time=<GMT>&MAC=<base64encodedkey>

In the first example: production and test urls, site Id, base64 encoded keyword, (made up of vendor key and vendor password), path and file names for input and output files.

In the second example: production and test urls, practice name, vendor name, vendor password, practice user name, base 64 encoded keyword (madeup of vendor name and vendor password), xslts that will be used to create the xml to send to the website, API version, calling application.

I am also wondering about how to save the keywords ... vendorSiteId, rcopia_portal_system_name, etc.  They will probably never change so I could just hard code them ...

We are moving on to our fourth integration with 3rd party software and I know of several more that are on the wish list, mostly health insurance claims processing clearing houses.  Each was implemented differently ... and I think I should work toward a uniform implementation for ease of support.

Thanks again for any and all help and insight.

[comment]

I am afraid that doesn't allow me to determine whether adding a new API would be considered an upgrade of your solution (perhaps justifying a new version number) or just extending an existing functionality. 

Only the former case would justify modification of the schema. And even then, you should try to reuse your existing resources (tables, relationships, scripts, etc.) instead of adding new ones. So I think you want, as much as possible, to use a single table for all your APIs. 

There are several ways to handle the problem of "not all APIs fit in the same table structure" - I believe I have listed them all here:
https://fmforums.com/topic/104606-best-practices-for-portal-showing-multiple-forms/?do=findComment&comment=473949

 

Edited October 18, 2019 by comment

[Wim Decorte]

Sounds to me like nothing more than a preferences / settings table with a record for each API that you need to connect to.  Most APIs wil have things in common, like a base URL, and id and secret (or username and pw, or a token), a set of routes, perhaps some template payloads.

If you are distributing this application then I would shy away from schema changes for this purpose, easier to upgrade data than it is to have to push out new files and migrate data.

[JMW]

Thanks for the input!  I will have to look at the existing table initially created for a specific API and see if I can make it work as a preferences / setting table.  Currently, there is only one application using it.  The other two have their data hard-coded and assigned to variables since they were written for specific customers and haven't been made widely available yet.  I did this with knowing that at some point I was going to have to store the data in fields in a table.

The software I write for has a somewhat interesting model.  We have 150+ offices who have a version of our software installed and each office's version has been customized for their specific needs.  But, I know that over time more and more offices are going to want to use the software I have been writing to interface with 3rd party software so I'm trying to figure out how to design and code for ease of use and support in the future.

From your comments it sounds like I should figure out how to make a single preferences / setting table and figure out how to make it work for all the interfaces.  One more question, should I try to modify the existing preferences / setting table to be more generic or just create a new preferences / settings table and use it in the future?  The field names in the existing table has fields names specifically for the application it is interfacing with.  Using those names in scripts for other APIs would make the code read strangely, if that makes sense.

These may seem like simple questions but I don't have many people to talk with about database design questions.

Again, thanks for all your input.

[comment]

1 hour ago, JMW said:

should I try to modify the existing preferences / setting table to be more generic or just create a new preferences / settings table

Not sure it makes much of a difference. In any case, make sure you have a backup before you start experimenting.

[bcooney]

You might want to look at what we did with Karbon. We namespace the API config fields in a single record table "System".

[JMW]

19 hours ago, bcooney said:

You might want to look at what we did with Karbon. We namespace the API config fields in a single record table "System".

Thanks, I will do that.