many to many or single table

[Ortix]

So I'm rebuilding an existing joomla component in laravel and this time I want to get it right. Hopefully my explanation is not too complicated.

 

I have this situation:

 

I have an order. The order has many products. The product has many parameters in the form of input fields such as radio, select list and text (think of color, size, engraving, logo select). An order also has some parameters such as address etc.

 

My question is how I should structure this considering the following:

 

1. The parameters of either an order itself or a product can change from time to time. Sometimes I need to add several fields for a certain amount of time.

2. Each product has 1 or more production pages (production steps) where some of the parameters set by the order need to be visible. Each production step has its own set of parameters (3d model, logo file). This means that the step1 page should show its own parameters INCLUDING a predetermined set of product parameters. So the columns could look like this: color - engraving - logo select - 3d model - logo file.

3. Each production page lists all the products belonging to that step.

4. Each product can belong to 1 or more production steps.

5. A product is only visible at 1 step at a time. The steps have a specific ordering.

 

A little note: it's not a usual webshop, but rather we sell custom sports products. Currently we have 3 products where 1 product has 2 production steps and another has 3 production steps.

 

Don't get me wrong, I have a working application for this, but currently it's just a database mess. The application kept growing as per bosses request so thinking it through was not possible  at the time.

[Psycho]

If the structure of data can change over time, you definitely have to think ahead. But, the solution isn't that difficult. Also, "how" the data is provided by the user (text, radio, etc.) doesn't necessarily impact the database. The selected value is the selected value.

 

For Orders, create a table with the data you know will ALWAYS apply to orders. Order No, date, user, etc. etc. Then create a 2nd table to hold custom data associated with orders. Each record will hold one value associated with an order. But, you also need a 3rd table that will define the 'current' custom fields that should apply to orders. As you add/remove fields from this table it would drive the options available when processing a new order. Then when an order is saved you add records for each value to the custom data table with a foreign key back to the fields table.

 

Ass for products and their production steps. I would have one table of all the possible production steps. Then another table that will associate each product with the production steps that apply and the order they belong.