[RESOLVED] Best Way to JOIN Tables

[lalloo]

I have 3 tables in MySQL database:

table team_members
mem_id, mem_name, mem_level

table projects
proj_id, proj_name, task_id, team_members

table tasks
task_id, task_name

team_members field in table projects has a comma separated list of mem_id who are part of the project team (e.g.: 7,11,18,21).

Using PHP, what would be the best way to join the 3 tables to get a list of projects where a member is part of the team, like below:

Project Name, Project ID, Task Name

So if a member with mem_id "11" is logged in, s/he will see a list of projects where s/he is invloved in (i.e., where team_members field contains "11").

Thanks

[bradgrafelman]

You should first normalize your DB schema to at least the first normal form (1NF). First normal form says that no relation (a.k.a. "table") has a multi-valued attribute (a.k.a. "column"). In other words, no comma-separated lists.

Instead, you'd create a separate table to represent the relation. For example, you could create a new table called project_members that has two columns (foreign keys): proj_id and mem_id.

If project #1 had members 7, 11, 18, and 21, then you'd see four tuples in that table; (1,7), (1,11), (1,18), and (1,21).

After you normalize your DB schema, creating the necessary JOINs to get the information desired is quite easy.

[lalloo]

Thanks, bradgrafelman. Really appreciated.