I am developing an app that keeps track of items going in and out of factory.
For example, lets say you have 3 kinds of plastic coming in, they are mixed in various ratios and then sent out as a new product. So to keep track of this I've created following database structure:
This is very simplified overview of my SQLAlchemy models:
IN <- RATIO <- OUT <- REPORT ITEMS -> REPORT
IN are products coming in, RATIO is various information on measurements, and OUT is a final product. REPORT is basically a header model which has a lot of REPORT ITEMS attached to it, which in turn relate it to OUT products.
This would all work perfectly, but IN and RATION values can change. These changes ultimately change the OUT product which would mean the REPORT values would change.
So in order to change an attribute on IN object for example I should copy that object with that attribute changed.
I would think this is basically a question about database normalization, because i didn't want to duplicate all the IN, RATIO and OUT information by writing it in REPORT ITEMS table for example, but I've came across this problem (well not really a problem but rather a feature I'd like for a user to have).
When the attribute on IN object is changed I want related objects (RATIO and OUT) automatically copied and related to a new IN object.
So I was thinking something like:
Take an existing instance of model IN that needs to change (call it old_in)
Create a new one out of it with some attributes changed (call it new_in)
Collect all the RATIO objects that are related to old_in
Copy each RATIO and relate them to a new_in
Collect all the OUT objects that are related to old RATIO
Copy each OUT and relate them to a new RATIO
Few questions pop to mind when i look at this problem:
Should i just duplicate the data, does all this copying even make sense?
If it does, should i rather do it in plain SQL?
If no what would be the best approach to do it with Python and SQLAlchemy?
Any general answer would suffice really, at least a pointer in right direction.
I really want to free then end user for hassle of having create new ratios and out products.
