> frizzle wrote:
> > Axel Schwenke wrote:
> > > "frizzle" <phpfrizzle> 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.
> > >
> > > [url]http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html[/url]
> > >
> > >
> > > XL
> > > --
> > > Axel Schwenke, Senior Software Developer, MySQL AB
> > >
> > > Online User Manual: [url]http://dev.mysql.com/doc/refman/5.0/en/[/url]
> > > MySQL User Forums: [url]http://forums.mysql.com/[/url]
> >
> > 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
Bookmarks