many-to-many database relationship

[Tiger99]

I am trying to develop an application which has a many-to-many database relationship like the following.

User Table
userid
user name
user info

User_Cds Table
user_cds id
userid
cd id

Cd Table
cd id
cd title
cd file

This is my first time developing an application with these kind of table relationship. I am not sure how the layout of my application should be to achieve the purpose to the relationship.

Can some one please help me out

Thanks in advance

[barand]

The many-many relationship implies that one user can have many CDs and one CD can have many users.

By reading the user_cds table with the user as the index, you can see which CDs they used. Alternatively, by using the CD as the index, you can see who has used each CD. In a library situation you could add a date, say, and see when.

hth

[ednark]

barand is exactly right:

you want 'Users' to have a many to many relationship with 'CDs'

see if following this logic helps:

you could put an extra column in USERS which says what cd they have, or put an extra column in CDS saying which user bought it

this would give a one to one relation... which you don't want

the solution is to create a third table : which you have already done!!!

USER_CDS
- user_id
- cd_id

as long as you do not restrict either column in this table to a unique value, this holds your Many-toMany- relationship

if you want to pull out from your database how many cds user 1 has, you ask the USER_CDS table

SELECT cd_id FROM USER_CDS WHERE user_id = 1

or how many users bought cd 5

SELECT user_id FROM USER_CDS WHERE cd_id= 5

so in general to make a Many-To-Many relationship between any two tables, you need a third table.

you have this already! so i don't see what your problem is, you have the information stored in your database, you might not know how to pull it out though.

you may want to start reading up about Joins now so you can see how to combine the information from these three tables into one query result