frizzle wrote:
> Axel Schwenke wrote:
> > "frizzle" <phpfrizzle@gmail.com> wrote:
> >
> > > songs -> id, artist_id, song_name
> > > lyrics -> id, song_id, lyrics
> > > genre -> id, song_id, genre_name
> > >
> > > Now to delete the song (1 record), *possbile* lyrics (maybe 1 record,
> > > maybe none), and *possible* genre-links (max unlimited records),
> > > i use the following query:
> > >
> > > DELETE s.*, l.*, c.*
> > > FROM `songs` s,
> > > `lyrics` l,
> > > `genres` g
> > > WHERE s.`id` = $id
> > > AND l.`song_id` = $id
> > > AND g.`song_id` = $id
> >
> > Why not using 3 DELETE statements? Alternatively (much better) you may
> > go for real foreign key constraints and the ON DELETE CASCADE option.
> >
> > http://dev.mysql.com/doc/refman/5.0/...nstraints.html
> >
> >
> > XL
> > --
> > Axel Schwenke, Senior Software Developer, MySQL AB
> >
> > Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
> > MySQL User Forums: http://forums.mysql.com/
>
> I haven't used foreign keys, because i have no experience with them
> (yet).
> Might be a good time to start with it ...
>
> Frizzle.

Also, consider revising your structure.

A song (or at least any given version of a song) can only have one set
of lyrics. Similarly, any given set of lyrics is likely to belong to
just one song - so these might as well be stored in the same table.

Genres exist independently of specific songs - and songs can belong to
more than one genre so there should be a separate table that relates
songs to genres - comprosing of a primary key made up from the song_id
and the genre_id.

Thinking about it you could go further. Songs are just words and music
written down. Each song has a composer (or composers) who may or may
not be the performing artist.
So you might instead have a table of 'tracks' where a track consists of
a track_id,song_id,(performing)artist_id, and length - and it would
then be this track_id that you'd associate with a genre.

So the structure might then look like this...

songs
song_id | song_name | (composing)artist_id
1 | 'Rocket Man' | 1
2 | 'Yesterday' | 2
3 | 'Something' | 3
4 | 'What'd I Say'| 4

genres
genre_id | genre |
1 | pop |
2 | rock |
3 | blues |

artists
artist_id | artist
1 | Bernie Taupin & Elton John
2 | John Lennon & Paul McCartney
3 | George Harrison
4 | Ray Charles
5 | The Beatles
6 | Elton John
tracks
track_id | song_id | (performing)artist_id | length | released
1 | 1 | 6 | 3:54 | 1972
2 | 2 | 5 | 3:03 | 1965
3 | 3 | 5 | 4:01 | 1969
4 | 4 | 4 | 3:34 | 1959

trackID_genreID
track_id | genre_id
1 | 1
1 | 2
2 | 1
2 | 2
3 | 1
3 | 2
4 | 3

albums
album_id | album_name | released
etc | etc

albumID| track_no| track_id |
and so on

hell, i'm going to write my own music db