Indexing or relational problem??

[thawes]

I have a table that contains info about projects like "title, description, image". Then a table for users which has fields like "userid, username, password". The question I have is that I'm trying to develop a system so that when a user logs in they will see only some specific projects from that projects table. However the same project can be associated with many users and users and projects are added on the fly. I'm pretty sure I need to setup a relational table that connects these but I just can't figure it out.

any help would be great!

[devinemke]

a classic many-to-many relational database structure. 3 tables:

projects
project_id
title
description
image

users
user_id
username
password

links
link_id
project_id
user_id

the project_id and user_id are unique primary key fields in their respective tables. the links table links them together.

so if you want to know what users are linked to project #38:

SELECT users.*, links.project_id FROM users, links WHERE users.user_id = links.user_id AND links.project_id = 38

or what projects is user #44 linked to?

SELECT projects.*, links.user_id FROM projects, links WHERE projects.project_id = links.project_id AND links.user_id = 44