0

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.

Bill of Material in a form looks like thisenter image description here

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. enter image description here

My questions:

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.

2 Answers 2

2

Before building this you should get some input from the business on how BOMs work. Maybe you oversimplified the description above for the SO post, but BOMs can get complicated fast.

Regarding the design, the first thing that comes to mind is that a component can have subcomponents which are themselves components. A fishing pole has subcomponents of reel, handle, fishing line, etc. The reel component has subcomponents of spindle, handle, axle, screws, etc. The handle has subcomponents of lever, grip, screws, etc. A BOM for a real product can have many layers and will explode into a LARGE number of small parts, which is what I assume you mean by raw materials.

From a technical standpoint, I would not put the “in process” products and the “completed” products in different tables. It creates an additional job of copying rows from the “in process” to the “completed”. Considering how two different products can use the same basic components (such as the same screw) , what do you do when you try to copy the completed BOM from the “in process” table to the “completed” table and that screw is already there? How do you reconcile that?

The only difference between “in process” and “completed” is a Boolean flag, probably a separate flag for each party that is adding data, so you would have an EngineersDone flag, a Patterners Done flag, and RawMaterialsDone flag. When all 3 are true, the product is done. You need these flag on the “in process” table anyway since none of the three parties can be expected to add their info without errors in a single session. They are going to put in a few items, come back the next day and fix an error, then come back the next day and add a few more. They need a button to press to indicate they are done.

Having a single table with complete flag also dodges the troubles you would get if the app goes down while copying over data from the “in process” table to the “complete” table. How do you reconcile the data if half of it got copied and half didn’t? How do other processes know that the product you were moving is not really valid yet? You can put the DB inserts in a big transaction but that could be thousands of rows. The DB would not be happy.

As far as adding nulls for missing raw materials and patterns, that’s not how foreign keys in databases work. The parent table doesn’t have a pointer to all child records, the child records point to their parent. Therefore there is no “raw materials” field to set to null – that field shouldn’t exist.

1

I'm in full agreement with Brad Irby here. Understand the business first, don't just ask them what they want to see on the screen, ask them about the domain that you're building an application for. What little I've dabbled with BOM's I've also seen they get quite complicated, and there are many closely related but complicated aspects like stock management, ordering and order tracking, billing, etc...

Specific to what you are asking:

1) if the primary difference between two tables is the status of the items in those tables, it is generally better to use a status column than a separate table. Ask yourself this question: what can't you solve with a status DRAFT / COMPLETE on the bill of materials table?

2 and 3) it sounds like you're thinking too deeply technical about what is a functional problem: what do your users want to do in order to "move it forward". Is just filling out all fields enough, or do they want an explicit button "Complete"?

4) if the different levels are likely to have different attributes, they should become different tables linked with foreign keys.

Your Answer

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

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