This is more of a general design pattern and UX question than a technical one.
I am working on a SQL database which contains product inventory (using Django). Currently, this inventory is managed by another (non-technical) employee via a spreadsheet which contains all inventory information and is updated daily. In order to support this employee's workflow, I would like to have the option of uploading the spreadsheet to the server, have it processed, and reflect the changes in the inventory (SQL database). Here's the catch: The SQL database can also be updated by customers via our API. When a user purchases an item, it is removed from the inventory, meaning that our employee's spreadsheet becomes out of sync. Furthermore, if users are modifying the database and the employee does not have the most up-to-date version, when the employee goes to upload their spreadsheet, the changes made by users would be overridden.
Here are options I am considering:
- Require our employee to adopt a custom interface to manage inventory and ditch the spreadsheet. I am hesitant on this option because I want to support their existing workflow.
- Use an API like on Google Drive to update the spreadsheet programmatically whenever the database changes. Somehow this must also work in the opposite direction: when the employee updates the spreadsheet, the server must be made aware and update the database accordingly. My concern with this (specifically the latter aspect) is data integrity: it is easy for the employee to enter invalid data on their Google Sheet, resulting in failed updates to the database, and not much option to inform the employee of the failed insertion.
- Ensure that the employee downloads the latest state of the database before uploading a new spreadsheet. I am not sure how this can be enforced, and there seems to be a lot of room for error here.
Overall I am not sure the best way to proceed. Has anyone ever dealt with something like this, and if so how did you resolve it?
Aucun commentaire:
Enregistrer un commentaire