4

I can get some financial data from our vendor via their API. In this case API is too strong a word. Basically we can get only an array of orders for a given time range. What we paid them for the product, what they charges us for shipping, etc.

To report on and analyze this data I want to store it in a table. I have some concerns:

  • What if i load 1 Jan - 31 Jan today, and then next week they change some data in that range? With thousands of products sold daily combing over that to look for necessary updates could be intense.
  • How should I manage adding new data? I'm thinking to get new data mid-month for the previous month. This vendor's practices are not the best, I'm concerned they'll add some things one Monday that they forgot to enter the previous Friday. I think if I get today's data every day the likelihood of changed data in a past period rises.

Appreciate any advice from folks who've faced a similar issue. Maybe people do this all the time and I'm worried over nothing. Just would like to get some advice before I proceed.

Improve this question
5
  • 2
    I would stage the "raw" data ... compare it to what's already in production ... and then push CRUD changes according to a predefined set of business rules. This would require that you have a solid model in the first place.
    – swasheck
    May 17, 2012 at 18:34
  • Also check out this (dba.stackexchange.com/q/1981/1342) as this may be a similar situation (natural data to something you have to track changes with).
    – Chris Aldrich
    May 17, 2012 at 19:26
  • Add new posting date/edit date columns with each record in addition to invoice date/payment date. Then pull data based on these new columns.
    – Raihan
    May 17, 2012 at 20:44
  • From what you wrote I suppose there is no combination of fields in the array that could be used as a primary key. That's a pity... On the other hand, you probably can store the whole array monthly and compare it to the current one on application level. This can be either easier or harder than comparing rows on database level, and you still need a bit of luck.
    – dezso
    May 18, 2012 at 8:14
  • thanks for the feedback. @ChrisAldrich that was helpful. I'm intrigued by the approach of mapping the natural keys to a new integer key. I'm still combing to see if there is a natural key that is less than the entire row.
    – microsaurus_dex
    May 18, 2012 at 17:03

1 Answer 1

Reset to default
1

Advice:

  1. Normalize data stored.

  2. Consider carefully and figure out your write model. For orders, can you overwrite? do you need to mark superceded and append?

The above are generic data entry pieces of advice, but regarding automated application to application interfaces, you have the issue of erroneous data entry. To deal with this it is probably better to accept orders etc. but have the invoicing done by humans, who can cross check with the real world. Also this keeps people in control of your books. So I would add to that:

  1. Separate automated entry from homan review and approval.
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.