I am building a Data Collection Web Application with Flask and MySQL Database. It is basically a creating a Bill Of Materials for the organization for a new product.
This application is giving three interfaces to three different groups ie:
a) Engineers - They will be adding ParentPartName and Components attached to that parent part b) Patterners - They will be putting patterning info related to each component c) Raw Material people - They will be putting information related to raw material for each component.
So in summary they all filling up this form in different pieces but the end product should look like above.
Also we need to push all the filled/completed data to the MySQL database table (Lets say BillOfMaterialsTable) only after everything is complete or the whole form is filled
But before pushing the data to the final table i would like to keep the data in a table meant for the open forms (forms not filled). You can look at it below like this. When the form is open the data resides in the Web app table and then when once the form is complete, basically when everything is filled then it goes to the BillofMaterialsTable.
1) Is it a good idea to have a separate table for Open forms?
2) Should I keep all the values (Raw Materials and Patterners Info) to Null in the Web App table when Engineers put in the new ParentPartName and Components as obviously they wont be putting any Subcomponents in there? I think it would be nice to keep values null as when the values for certain parentpart's subcomponent is not null then you can put it as complete and then can push it to the final table.
3) What would be the logic if some value in the subcomponents have to be null for some reason?
4) What would be the table structure for both the tables? Should it be just like the form above?
Any other advice on the design/architecture of my project will be appreciated.