I have 1 user table and want to associate it with multiple other tables depends on if user have added such information (modules) and want to know what is the best way to do it.
main table (id,name,email,gender, etc ...)
- conditions (id, user_id, name, etc ... )
- results (id, user_id, desc, etc ... )
- tests (id, user_id, date, etc ... )
Currently, I'm thinking of adding the a column to the main table (called modules) and add the table names as a comma separated string or array.
(id,name,email,gender, etc ... , modules)
Retrieve the field for each user, find the table names on the list and start looking for rows on each table that match the user id.
I want to know if there's a better way to do this. Right now it seems very inefficient and will require many queries for each user.
Update: I want to create something like a profile page that go through the different tables and list all the conditions/results/etc for a particular user in its own widget/box/div.