Profile Page Use Case

Question

The profile page of website contains these kinds of fields for any user-

1. One to One Fields - Name, Age, Gender
2. One to Many Fields - Job History, Education
3. Many to Many fields - Language, Award , Committee

Tools - MYSQL and PHP [ but open to changes ]

I decided to create one user table.

tbl_user -> PK - user_id , FK - profile_id 

All fields which occur for once for a user , I add them as attributes to table tbl_user_profile.

tbl_user_profile -> PK - profile_id 

Then , to satisfy One to Many kinds of relatioships I create a bunch of tables - This can easily grow over period of time ..

tbl_user_jobs , tbl_user_education
FK - user_id [ INNO DB ]

And then I create a bunch of tables as ref tables for MANY_TO_MANY modelling -

tbl_ref_awards, tbl_ref_languages, tbl_ref_committee
PK = REF_ID

tbl_user_languages, tbl_user_awards, tbl_user_committee
FK - user_id , REF_ID

Now, I have to render the profile page with as many details just mentioned. On Page loads every time, I have to join tables tbl_user_* tables with tbl_user and populate in the page.

I am speculating if this design is OK first of all ? Second, with as many tables being joined [7 times] on page , I see a drag already in page loading and would be very good to hear on possible improvement in database design in above use case. There are already indexes and FK existing. No cache added yet anywhere.

Answer 1

This is a pretty reasonably approach to modeling your data; the only comment I'd make is that instead of naming the primary key for your reference tables "REF_ID", I'd name them after the table itself - award_id, for instance. Ditto for the foreign keys.

This is a matter of preference, of course, but it's more consistent - you have named the primary key of the user profile table "profile_id".

On modern hardware, with appropriate indexing, a 7 table join shouldn't be a performance problem unless you're dealing with hundreds of millions of records. Please post an EXPLAIN of the slow query and let us see if we can help.

However, practically, it may be easier to run several small queries to populate the page, rather than one big query. This is because a 7-table join will, of course, repeat all the "one to one" data for each row, and that just creates a bit of a messy data handling layer. I'm imagining that right now, you're getting

user_id user_name job_name language_name
-------------------------------------------
1      Bob         Waiter   French
1      Bob         Waiter   German
1      Bob         Waiter   Italian
1      Bob         Plumber  French
1      Bob         Plumber  German
1      Bob         Plumber  Italian

Instead, I'd retrieve the user profile in one query, the user jobs in a second, the user education in a third and so on. This makes your PHP code much neater, and reduces the amount of data you're shuffling between your database and your web application.