Simple 1:M Relationship Database, Suggestions?

[MDWeezer]

I am creating a simple database containing 2 (or possibly 3 tables, thats why I need advice).

 

My first table is a users table, primary key will be a auto increment userid.

 

My second table is a album table, its primary key will be a albumid and secondary userid.

 

I would like to be able to add as many albums as I would like for any particular user and relate them using the unique userid. So albums 2, 5, 35 all belong to userid 34. Is this the way to do it? Or should I create a third table?

 

The third table would contain both primary keys from each table. userid from the users table and albumid from the album table.

 

Also which is the best way to query to lookup? So if I want to view all albums for a particular user. Would using the SQL join commands work fine or is there a better method?

 

I\'m a SQL newbie but would love to figure this out, I could get it work on my own, I would just like some expert opinions on it.

 

Thanks all in advance. I look forward to reading some responses!

 

-Matt Williams

[DylanBlitz]

you can do it either way. I guess depending on how your write the query and how many hits the site gets would tell how you\'d code it. If it\'s not going to tax the server too much I\'d say go with the 2 tables. Thats kind of how I have mine. I have a band table and an album table. In the album table it has a unique id for the album and it also has the id for the band in it.

 

On the query, I\'d do something like

 

select username where user_id = $id

 

select album_id, album_name where user_id=$id

[Barand]

You onl need 3 tables if an owner can have many albums and an album can have many owners. In your case, the 2 tables are all that are required.