Jump to content


Photo

Simple 1:M Relationship Database, Suggestions?


  • Please log in to reply
2 replies to this topic

#1 MDWeezer

MDWeezer
  • Members
  • PipPip
  • Member
  • 13 posts

Posted 08 October 2003 - 04:46 AM

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

#2 DylanBlitz

DylanBlitz
  • Members
  • PipPipPip
  • Advanced Member
  • 99 posts
  • LocationOC Baby!

Posted 08 October 2003 - 05:03 AM

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

#3 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 19 October 2003 - 07:33 PM

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.
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users