Delete against multiple tables + And / Or - MySQL

[frizzle]

Hi there,

a part of my music site exists of three tables:
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

Which works _IF_ the song also has lyrics AND genres.
Now of course, a song also has to be able to be deleted if it
hasn't got lyrics, or genres.

I tried replacing AND in the query with OR, but that kind of emptied my
songs-table ... :(

Any clues? Any help would be great.

Frizzle.

[Axel Schwenke]

"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]

[frizzle]

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 assumed a single query would be faster.
I will look in your options later on. Thanks a lot.

Frizzle.

[frizzle]

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.

[strawberry]

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

[frizzle]

strawberry wrote:

> 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

First of all, i decided not to put the lyrics in the same table,
because a while ago i learned that mysql reads all data, and then dumps
what's not in the SELECT statement.
I figured including the lyrics in the songs table would be quite large
in comparison of the rest of info; some title, artist_id and duration
....

Please do correct me if what i thought about the selecting & dumping is
wrong ...

Secondly, what you suggest is sort of what i have. I will roughly
sketch it below.
The ideas you have, i also had. I want to build an extensive music DB
in the near future.
This one right now is just for some simple track listing. I want to
keep it as clean as possible, so the effort of adding a song doesn't
need to be preceeded by adding albums, track no's etc.
( Did you already think of a way to handle albums made by various
artists, or songs with the cooperation of another artist, or even
multiple, or bands constructed for the occasion ? (like Band Aid)
People swapping bands, previous band members, People swapping
recordlabels, etc. etc. etc. The more you think about it, the more
comes to show, what a nice challenge the future holds for me ... :) )

Rough table sketch current structure:

- Artists -> Id, Name, some additional info
- Songs -> Id, Artist_id, Name, duration, etc.
- Genres -> Id, Name
- Moods -> Id, Name
- Combo_artist_genre -> Id, Artist_id, Genre_id
- Combo_songs_moods -> Id, Song_id, Mood_id


Frizzle.