At work we've designed too applications that allow the administrator to decide which fields a user's profile has. Each field can be modified, deleted or created, even first and last names which seems like a bad idea to me, but it's what the client wanted.
When we were first designing the database, well, when I was designing it I thought that using Wordpress's table structure for its posts would make sense. It keeps some information that will always be used in the wp_posts table and allows "meta" data to be added and stored in wp_postmeta, so that a developer could add options and information to a post without altering the posts table. So with that in mind, I created three tables, one was a basic user table with the following columns:
id email status type
1 firstname.lastname@example.org 1 2
2 email@example.com 1 3
The other was the meta table to store profile data:
1 First Name
2 Last Name
Where label is what shows up on the profile such as "First Name", "Last Name" etc. The third table is the user and profile data:
id mid userid value
1 1 1 'Test'
2 2 1 'User'
3 1 2 'Second'
4 1 2 'User'
Mid being the meta table's primary key, userid obviously the id of the user being stored, value the actual data.
All of this was okay in theory, but as we went about development, the amount of legwork we had to do to retrieve labels and values out of the tables was a little absurd. If we were just pulling first and last names, that's two simple joins to do, but if I want to pull out their name, where they live, their age the number of joins gets unwieldy and we were forced to pull all the available field labels out into a PHP array and then iterate over all the rows in the user_field_value table and match up labels to values for each user's id. I'm sure that there's a better solution to this problem than what we're doing - I did try looking up pivot tables in MySQL, but it looked like they were better suited to aggregating sums from a table. If anyone's done something like this before, or has any ideas, I'd appreciate it. What we have now works, but it's clumsy.