MySQL but don't know the column names before hand

Question

I am building an PHP/MySQL app and I am allowing users to create their own custom (as much as they want) profile data (i.e. they can add any amount of info to their profile with additional textboxes, but there is a "CORE" set of user profile fields)

For example, they can create a new textbox on the form and call it "my pet" and/or "my favorite color". We need to store this data in a database and cannot obviously create columns for each of their choices since we don't know what their additional info is before hand.

One way we think that we could store all "addidional info" they provide is to store their additional info as JSON and store it in a MySQL text field ( I love MySQL :) )

I've seen Wordpress form builder plugins where you can create your own fields so I'm thinking they must store the data in MySQL somehow as NoSQL solutions are beyond the scope of these plugins.

I would love to stick with MySQL but do you guys think NoSQL solutions like MongoDB/Redis would be a better fix since for this?

Thanks

Answer 1

One way to approach this is to use a single table using the EAV paradigm, or Entity-Attribute-Value. See the Wikipedia article. That would be far tidier in most respects than letting users choose a database schema.

Answer 2

You could create a table of key value pairs where anything not in core would be stored. The table would look like: user_id, name_of_user_specified_field, user_specified_value;

Any name_of_user_specified_field that starts showing up a lot you could then add to the core table. This is referred to as Entity-Attribute-Value. Please note, some people consider this an anti-pattern.

If you do this, please add controls to limit the number of new entries a user can create or you might find someone stuffing your db with lots of fields :)