I've attached a diagram of my database structure. My issue is two fold. I need to be able to dynamically add new fields into the database attributeinputs table as product types diversify from the current product types.
1) I can figure out how to dynamically add a new field to a table via php but ... what about a situation such as the voltage or body color (two examples).
Right now, both have specific values that I need to control ie; Voltage can be 127v or 127-277v or 277v. Those are the only choices I want for each product and each product can only have one. Body color values are: bronze, black, white, gray or RAL. However, each product can have multiple choices of body color. How do I dynamically add that type of field into a database.
I've manually set up a voltage and bodycolor table which will contain the specified values. The main table uses the foreign field to constrain the values that can be entered for a specific product. Does that makes sense? (I also understand I will probably need a junction table for the many-to-many bodycolor).
Do I create the new table and then create the new field with the foreign key in the attributeinputs table to restrain the values for the new field?
Or have I gone off the rails here?
2) Assume also that I have a form that adds/updates a new product's data to the attributeinputs table. I would like to dynamically generate the form fields from the database attributeinputs table fields. How do I connect the field with any required values. Example below.
ProductCode field is a standalone input field. That becomes a simple input tag.
Voltage would be a select with options 127v or 127-277v or 277v.
Bodycolor would be checkboxes with bronze, black, white, gray or RAL.
Is there someway to use the database structure to have the php recognize that voltage/bodycolor in the attributeinputs table have a foreign key constraint and to look for the voltage or bodycolor table for the values?
Hope these questions make sense. If I'm totally off base, let me know. Please don't tell me to use a framework that has already done this. This is a LEARNING project and I want to know how to do this. I'm not necessarily looking for code. Just for guidance about the approach.