I did a quick check and I could not find any questions similar to the one I am about to ask. I wish I could have been more specific in the title as it seems like a question that has been answered just shy of a million times.
I currently am having the dilemma of not knowing how to setup a schema for the following problem for a web app:
I have an online form that has users order custom tshirts with the following fields: Size, Color, Pocket.
Each user in the system can have different options from the first and the fields are dynamically generated
Examples: User 1: Size[Small or Medium], Color[Grey or Black], Pocket[Square or Rectangle]
User 2: Size [Medium or Large], Color[Blue or Red], Pocket[Square or Triangle]
Every user can have completely different options and the options for the size, color and pocket are independent of each other.
Is this a possible solution to this problem?
Table:
id | user_id | Size | Size_options | Color | Color_options | Pocket | Pocket_options
I feel there is a better way to break these out even more and would be nice to have field for option value.
I will be using a MySql database.
This is just a small example of my problem and have forms with 50+ fields that all need to interchange like this one but are textboxes, checkboxes, radio buttons, and select drop-downs.
Thank You so much for reading and any advice that is given and I apologize for the length. I wanted to be clear on the issue I am trying to solve.
What you need is seperate table that stores properties
Properties
id
property name
property description
What goes in here? Things like Pocket shape, pocket color, shirt color, collar type etc.
Then you need another table to link the above with a particular item of clothing ordered by a user
cloth_properties
user_id (or more likely order id)
property_id (foreign key properties)
property_value
This allows you to be in full control of what properties are being set or changed by the user and also makes it possible for you to add new properties dynamically.