Welcome to WebmasterWorld Guest from

Forum Moderators: open

Message Too Old, No Replies

Structuring a database to allow for custom fields

7:11 pm on Aug 18, 2013 (gmt 0)

Full Member

5+ Year Member Top Contributors Of The Month

joined:Sept 30, 2009
votes: 1

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.
8:06 pm on Aug 18, 2013 (gmt 0)

Full Member

5+ Year Member

joined:Aug 16, 2010
votes: 20

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.
12:56 am on Aug 19, 2013 (gmt 0)

Full Member

5+ Year Member Top Contributors Of The Month

joined:Sept 30, 2009
votes: 1

Thanks bhukkel. I'm leaning towards the same table because it will make it so much easier to work with the data.
10:32 pm on Aug 20, 2013 (gmt 0)

Senior Member from US 

WebmasterWorld Senior Member whoisgregg is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:Dec 9, 2003
votes: 0

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.

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

Featured Threads

Free SEO Tools

Hire Expert Members