Model for custom fields in database

[Adoshev]

Hi everyone.

In my application i would like to have some kind of custom fields, which can be defined by administrator. For example, we have two types of goods: food and toys.
Those two types has common fields, e.g. name, price, amount.
I'm planning that administrator can create custom fields for every type in my app. For example, toys can contain field fabric, size, allowable age. And food can contain taste, color and others. In my plans, that administrator can create any amount of types and create any amount of fields for every type.
My question is i can't imagine how to represent all this data in my database.
Please help me with this. By the way, i'm using PHP and MySQL. Let me know if you need more information from me. Thank you in advance

[Adoshev]

Anyone?

Use the CREATE syntax for creating a table. There may be better ways of doing this as opposed to creating a new table for each type.

You could use a common set of fields:

id / name /price / amount

And then tag one more:

type_id (1 = toys, 2 = food)

Depending on the number of fields you have, I'd just shove them into the big table.

fabric, size, allowable age, taste, color

In your input and edit forms for products, simply have a dropdown or radiobuttons for toy/food. These just toggle displayed form fields (via ajax).
On submit, the POST var picks up food or toy and applies filled data accordingly.

It makes sense for a DB to hold common data items, otherwise you'll be creating hundreds of different tables, possibly one for each product, which would be clearly ridiculous.

You can always have an "add field" function in your form. You could then change the table to include this extra field with the ALTER syntax. The admin would have to stipulate the data type, e.g. date/int/varchar/text etc.

Anyway, just my 2p.