homepage Welcome to WebmasterWorld Guest from 54.205.144.54
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Visit PubCon.com
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
Structuring a database to allow for custom fields
ocon




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

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.

 

bhukkel




msg:4603072
 8:06 pm on Aug 18, 2013 (gmt 0)

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.

ocon




msg:4603121
 12:56 am on Aug 19, 2013 (gmt 0)

Thanks bhukkel. I'm leaning towards the same table because it will make it so much easier to work with the data.

whoisgregg




msg:4603694
 10:32 pm on Aug 20, 2013 (gmt 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.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved