An efficient database design for recording complex contact/configurator form

Question

I know it's a quite generic question and probably opinionated. I don't know where to ask. Maybe you could point me to the right direction.

Currently I have a very big 'configurator', 10 steps long, online form, with 'substeps'. It is for client asking quotes for custom screwdrivers components applications (called 'auto-feed tightening modules'). Say about 50 fields/props per-submission/record.

WHen compiling the form, the user can:

• Add as many 'screws' he wants (each screw described by some subfields)

• Add as many 'components' he wants(each component described by some subfields) > and for each component, say what (above) screws it is related to

So when the form is submitted, we have a record composed like this:

• a lot of 'shared' fields
• some 'screw' fields group
• some 'components' fields group
• and screw and components must be somewhat associated (each component can have 1 or more screw)

All of this should be saved in a database.

Now, since:

• we don't know how many screws and components will be added in a single submission

• each screw and component has it's own set of fields (duplicated)

I cannot save each submission in one single row/record.

So, how to? Considering that then I'll need to show up the data somewhere else.

In the end, the real question: how should I design my db tables?

Answer 1

You have three questions here.

Storing multiple rows (of components) per form submission. This will happen in any process flow of a manufacturer. It is possible and you shall normalize by adding more rows per form (even for a component).

Secondly, each screw / component having its own set of (duplicate) fields can be handled simply by making those components refer to the existing data itself just by adding another sub-item id as required.

And for your main question of "how should I design my db tables", it is better if you could present a design and your expected bottle necks / issues / hurdles / etc. for which we shall suggest solutions.