Whats a good way to implement a variable number of custom fields in a CMS?

Question

The implementation used where I work uses about 10 columns in the table: CUSTOMFIELD1, CUSTOMFIELD2 etc. And every time I look at it, I think there must be a better way.

Not everyone will use all 10, some will use 0 and so there these empty columns just staring back at me, begging to be used.

So if there is a better way... ideally, I think it'd allow:

1. More than 10 custom fields. Theoretically, infinite. Practically, something sane like 15.
2. Each custom field could be given a name, set by the user.
3. The custom fields type could be set. Basic types like: string and number.

So far, I'm thinking of having a column named CUSTOM_FIELDS, which will store a path to a file containing the custom fields' descriptions, names, types etc. in a format like XML, JSON or YAML which I could transform back into an object. And that's just the custom field's definiton, there's the whole matter of how I save content that uses these custom fields...

Anyways, that just where my thoughts are now. Any ideas?

PS --- I'm of the mind that customising the application to suit the user would be more ideal than slapping things into custom fields, but I don't have that luxury & other times defining an extra 2 custom fields just works.

Answer 1

Not everyone will use all 10, some will use 0 and so there these empty columns just staring back at me, begging to be used.

One quick tip, don't normalize or denormalize databases because you have to scroll far to the side and think "man this is to big" Because this view is just an arbitrary view of the database table.

Answer 2

Create a separate table - CustomerFields with columns:

• customer_id (int),
• field (varchar),
• value (varchar),
• datatype (varchar)

Now by finding all entries with customer_id='CustomerID' in CustomerFields table you can assign as many variables as you wish. The only thing is that value is not realy a varchar, but can be an integer and should be parsed. But I think this is not that big problem taking in account how simple this schema is