Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

migrated from stackoverflow.com Mar 29 '13 at 14:42

This question came from our site for professional and enthusiast programmers.

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 '13 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 '13 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:

SELECT * 
FROM User U
   LEFT JOIN Conditions C ON U.Id = C.User_ID

Good visual representation of joins

share|improve this answer
    
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 '13 at 3:38

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.