Ask HN: Best practices in integrating with external data sources
Our tool needs to have student, faculty, staff & course data. All this data is already built into the student records system at each school. Most schools have different systems, with different tables, sometimes customized from the same product. Most of this data changes slowly, with a little, perhaps attendance, changing daily.
What's the best method for us to suck up this info with the least amount of integration effort each time?
Alternatives we are looking at are
1. Ask them to format & put the data out in XML of our format for us to suck in. Adv. No work on our part. Disadv. How do we handle diffs? Increases barrier to use for them. We have to write XML parser.
2. Ask for RO access to their dbs and suck it in via custom plugins for each college. Adv. We get direct access, no effort for them. Disadv. They are a little worried abt giving db access. Need to understand and write plugin - very high effort given diversity
3. Ask them to put it into our db. Use triggers on our db to put into our own tables. Adv. Easy for us. Disadv. Increases barrier to use. May not be easy for them to use.
4. Give them an api to add data. Adv. Easy for us, just need to define API well. Disadv. Increases barrier to use. May not be easy for them to use.
Have any of you done this before? How have you solved this? Your inputs would be much appreciated.
[edit] I am looking for technical solutions - we understand the market challenges.