Mysql – An efficient database design for recording complex contact/configurator form

database-designMySQL

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?

enter image description here

Best Answer

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.

Related Question