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 ...)

other tables

  • conditions (id, user_id, name, etc ... )
  • results (id, user_id, desc, etc ... )
  • tests (id, user_id, date, etc ... )
  • 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.

  • 3
    No, no, no, no, no! A "comma separated string" in a database is absolutely wrong. Don't ever even consider "comma separated" anything that you'll need to use in an SQL statement. You can always use an OUTER JOIN to see if something exists or not in another table. (You might want to find a book or web site about database programming and normalization. Google or Bing can help find one.)
    – Ken White
    Mar 29, 2013 at 2:34
  • Updated info. If showing info on "conditions" page, for example, I know that should use JOIN, but I'm trying to display info from all the tables on something like a profile page, and displaying each table info in its own widget for example. How would I loop through that if I was to JOIN all the tables?
    – Nathan
    Mar 29, 2013 at 3:46

1 Answer 1


You have no need to add a "modules" field to your main/users table. Just JOIN on the tables -- that's what Relational Databases are for. INNER or OUTER joins should be used depending on your needs.

For example, this will return a user with their conditions. If the user doesn't have any conditions, it will return NULL for those fields, but it will still return the user:

   LEFT JOIN Conditions C ON U.Id = C.User_ID

Good visual representation of joins

  • Updated info. I understand that when you try get info from a few tables, JOIN should be used. However, how would you loop through it if you join over a dozen tables at once, which is needed to display on something like a profile page? Thanks for the useful link, by the way.
    – Nathan
    Mar 29, 2013 at 3:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.