Designing database structure: Dynamic profile fields
Among the core features that I would like to integrate with the Glued! project is the dynamic profile fields for the users where users can selectively choose and edit fields in their profile. They will have the option to add fields, define field visibility (whether to display it publicly or only to selected user or group of users), and an in-place edit of the values of the fields.
With this in mind, I was faced with the problem on how I will design the database such that it will handle the dynamic profile fields of the user. I cannot predefine the fields and put them as a field(or column) on a single table. Then, I came up with the solution:
I created 3 tables. Let’s name them ‘users’, ‘profile_options’ and ‘profile_values’. To give you a short description on each of the tables and how they work:
- users - Your list of users. A user is identified by the unique auto-increment id generated by MySQL
- profile_options - Holds all available options that the users can choose from. This also defines the option type.
- profile_values - Defines the relationship of the user to the profile_options plus the values the users put into them
The ‘profile_values’ table holds all the profile fields of a user. Let’s say a user has a ‘name’, ‘birthdate’, ‘occupation’, ‘gender’ on his profile. This means that in the ‘profile_values’ table, selecting all the records for that user will return 4 rows (name,birthdate,occupation, and gender). Take note that the ‘profile_values’ will only contain the user id, the option id (the auto-increment id from ‘profile_options’), the value submitted by the user, and an optional column such as ‘display’. The column ‘display’ will define the visibility of the field (either public or only for a specific group of users).
With this database design, add a little fancy drag and drop, sortable, and in-place editing feature on the UI and you will have a kick-ass Web 2.0 application.
If you enjoyed this post, please consider to leave a comment or subscribe to the feed and get future articles delivered to your feed reader.
Comments
@Gaurav,
While it is true that it will be complex to code the report generation process, in my opinion, this is still acceptable. Bear in mind that you are implementing such design because you want your application to be flexible on the users perspective. Don’t you think web 2.0 sites nowadays doesn’t have complex database handling? You cannot get all the good things in life without compromising something. At the end of the day, complex queries on dynamic reporting won’t be a problem if you have good practice on development such as putting indexes, query caching, object caching, or maybe a database replication setup or a powerful hardware setup.
To whom it may concern:
Please send me some notes about database structure before we design the data base i wanted to know from where and how to start the database structure i would to clear my concept.
thanks.
Khyber Aryan.
I have just re-formatted my Acer Aspire 4310 to Hardy Heron and installed Kopete version 0.50.1 (Using KDE 4.0.3), but my webcam seem...
I agree with design and of course thats the most common solution that one will target.
Even I am thinking of implementing such system in my product.
But I have a doubt here, what will you do when you have to generate reports out of them. Wouldn’t it be a real complex thing to handle. I am afraid that this technique would be better to adopt when you have a data centric application in hand and that to when you have to implement extensive as well as dynamic reporting throughout your application.
I looking for a answer from your side
thanks
Gaurav Bhardwaj