Need Advice With Music Website Logic / Display Issue

i'v been scratching my head all d开发者_开发百科ay trying to figure out how to display duets on my music website.

basically i have 2 tables in my MySQL database.

singers : singer_id, singer_name.

songs : singer_id, song_id, song_name.

i display the songs like so: singer_name:song_name

i know i can make another table called finder with just: singer_id and song_id and if there is a duet i can just add the song to two singers. but is this the best solution?

isnt there a better more clever solution ?

This solution is fine but you shouldn't maintain two separate mechanisms to store the singer. I.e., drop the singer_id field from the songs table:

singers: singer_id, singer_name
songs: song_id, song_name
performers: song_id, singer_id

This lets you easily find all songs for a singer:

select song_id from performers where singer_id = $id;

And all singers for a song:

select singer_id from performers where song_id = $id;

You need to re-work your schema so that you can have a one-to-many relationship between the song and singers.

Easiest way to do this is to add a third table called singers_songs, with two columns, singer_id, and song_id. (Set them both to be the PK.)

Most of your songs will have one record in that third table, but for songs with multiple singers, there will be multiple records.