Mysql – Best way to set up database tables

database-designMySQL

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.

Best Answer

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

Related Question