Custom model fields for a CMS

Question

I've very fond of mapping my models using an ORM like Doctrine. For an upcoming project, I need to allow the user to add custom fields to my models. I was wondering the best way to go about doing this?

In the past I have done this by creating a database table 'custom fields' which looks like this:

field_id->pk
field_model->ida
field_type->varchar
field_name->varchar
field_data->text
field_validation->varchar

My main problem with this is the data is stored in a 'text' field, and the data could be as small as a single integer... it seems very wasteful to use an entire text field in that instance!

Is there another, better way to do this which I am missing?

Would appreciate any ideas!

Answer 1

What about something like:

id int
model_id int
name varchar
value_type enum('boolean','integer','text','float')
num_value decimal
text_value varchar

If you have some sort of value_type field (enum's just one way to do it), you can cast the variables out of your queries into your model members. Plus you can take advantage of SQL math, sorting, etc...