|Structuring a database to allow for custom fields|
I'm working on converting the backend of one of my website into a CMS to enable other people to easily create similar sites.
The primary database table is called 'places' and contains an id number, the name, description and location of many points of interest.
The original table used on my site contained many more columns, such as mailing address, phone number, hours, wheelchair accessible, kid friendly, and pet friendly statuses. However, that table was stripped of many of those columns into just the basics that the site would need.
I'd like to allow users the ability to associate their own customizable data to these places to tailor the website to their own needs. For me, I would like to add to associate the fields above, along with 70 other ones, but for someone else they might need completely different data.
Should I make the CMS alter the table, adding or removing columns as needed? Or should I create a separate table that has something like: id number, name, value.
Modifying the table structure would seem like it would be easier to maintain the database using backend tools like phpMyAdmin, would allow for easier data imports/exports, and would allow better structured types of data, but would seem to fracture the code base and make continued CMS development more challenging.
Creating a secondary table would seem like it would make all users have similar database structures and make it easier to continue CMS development but at the cost of databases more difficult to maintain and poor database structure.
I would very much like to hear suggestions on which way I should take the CMS development. Thank you.
I create software against a SAP product and SAP adds user defined fields to the same table. An user defined field starts with "u_".
As a programmer it is very easy because you dont have to join tables. Also importing and deleting records is a lot easier.
Thanks bhukkel. I'm leaning towards the same table because it will make it so much easier to work with the data.
Allowing end users actions to directly affect your table structure is a recipe for disaster, even if all your users were database administrators to begin with! Maybe especially if... ;)
I use a two table structure for cases like this. The main table has, in a nutshell, the "required" fields for the data type.
The secondary table has just a few columns like described in the OP; foreign_key, label, and value. I typically constrain the second table with a UNIQUE index on foreign_key and meta_name and then use REPLACE instead of INSERT/UPDATE.