I am asked to give a possibility to the administrator of the site to create attributes in the database tables. There are sellers and buyers on the website, and each seller when adding a certain product, fills out the needed fields for the specific product, and then publishes the product. I am kind of confused on how is this going to work. If every product has specific fields, then that would mean that if the site has 2.000 products, I will have 2.000 tables? I've never worked on such thing, so I really don't know how to handle this. Furthermore, on the admin feature to create attributes. Let's say the product is a tomato. The admin adds field for the tomatoes that is called "condition" and it has options such as "frozen", and "fresh". Then, when some of the sellers tries to create tomato product, they will need to choose if the tomato's condition is fresh or frozen. I thought of a possible solution such as creating a table that will hold the text of the , and then another table that will hold the text of the .
product_tomato ( product_id, user_id, name, description, condition)
product_select( select_id, product_id, select_text)
product_option( option_id, select_id, option_text)
So, this is how I imagined the tables for doing this. So, when the admin adds a field to the product table, I will add a column in the product table, then create new row in the prodcut_select table, and then list the possible options in the product_option table. But then I got confused on how to display that on the product page. How am I going to deal with that in the code, when I don't know what are the names of the columns that the admin created?
The wording of the question is very confusing, but I believe I get the gist of what you're saying.
No, you would not make a table for every single product, that would get ridiculous very quickly. You can handle this easily for multiple products with three tables.
Tables:
Product
Product_Attributes
Seller_Product
Let's take your hypothetical example of a tomato with conditions.
The admin decides that his site will now offer tomatoes as a product. He creates a product, and adds it to the product table. Then, he decides that tomatoes should have a "condition" attribute that has two possible values, fresh and frozen. Therefore, he would add two rows to the Product_Attributes table, with three fields (Product, Condition, Value).
Therefore, your tables would now look like this.
Product Table:
*Name |*
Tomato
Product_Attribute Table:
*Product | Attribute | Value*
Tomato | Condition | Fresh
Tomato | Condition | Frozen
Finally, when your sellers added items to the site store or whatever it is, you would have them enter the data into a form that grabbed the conditions and potential values from the Product_Attribute table for that product. In this case, there's only one attribute so they would just fill out the condition. Let's assume that there are two sellers, Jim and Tom, who sell fresh and frozen tomatoes respectively. The final three tables would look like this.
Product Table:
*Name |*
Tomato
Product_Attribute Table:
*Product | Attribute | Value*
Tomato | Condition | Fresh
Tomato | Condition | Frozen
Seller Product Table:
*Seller | Product| Attribute | Value*
Jim | Tomato | Condition | Fresh
Tom | Tomato | Condition | Frozen
This way, you could store a variety of custom fields about products using three tables. You should normalize or denormalize as needed, you may want a table for seller's products only and store their conditions in a separate table. Either way, the method described above would get the job done.