Music System: Artists with multiple Genres

[frizzle]

Hi group,

I have a small question:
I'm building a music site with lots of artists in them. Each artist can
belong to a certain genre of music: e.g. Rock, Pop, etc.

Artist table looks like: id, artist_name, genre_id, artist_info, etc.
Genre table looks like: id, genre_name

This way, in table artists, you can give an artist 1 Genre. But an
artist can be e.g. Rock AND Pop. How could i make a system that allows
multiple genres per artist? I don't wish to munually write them per
artist, because artists are also sortable by genre, and typo's would
mess things up.

Frizzle.

[Phil Cairns]

frizzle wrote:

> Hi group,
>
> I have a small question:
> I'm building a music site with lots of artists in them. Each artist can
> belong to a certain genre of music: e.g. Rock, Pop, etc.
>
> Artist table looks like: id, artist_name, genre_id, artist_info, etc.
> Genre table looks like: id, genre_name
>
> This way, in table artists, you can give an artist 1 Genre. But an
> artist can be e.g. Rock AND Pop. How could i make a system that allows
> multiple genres per artist? I don't wish to munually write them per
> artist, because artists are also sortable by genre, and typo's would
> mess things up.
>
> Frizzle.

This is a fairly classic problem: how to represent a many-to-many
relationship in a database. A genre can have many artist, an artist can
belong to many genres. You need to introduce an intermediate table that
holds the relationship between artist and genre. It could look something
like this:

artistgenre:
artist_id INT,
genre_id INT

Pretty simple table, but it means that you can remove the genre_id from
your artist table, and manage the relationship between artist and genre
separately from your actual data. It also makes the "which artists
belong to this genre" and "which genre does this artist belong to"
queries really simple. Creating such an association is as simple as
inserting a record, and removing the relationship is just deleting one.

Phil.

[frizzle]

Phil Cairns wrote:

> frizzle wrote:

> > Hi group,
> >
> > I have a small question:
> > I'm building a music site with lots of artists in them. Each artist can
> > belong to a certain genre of music: e.g. Rock, Pop, etc.
> >
> > Artist table looks like: id, artist_name, genre_id, artist_info, etc.
> > Genre table looks like: id, genre_name
> >
> > This way, in table artists, you can give an artist 1 Genre. But an
> > artist can be e.g. Rock AND Pop. How could i make a system that allows
> > multiple genres per artist? I don't wish to munually write them per
> > artist, because artists are also sortable by genre, and typo's would
> > mess things up.
> >
> > Frizzle.

>
> This is a fairly classic problem: how to represent a many-to-many
> relationship in a database. A genre can have many artist, an artist can
> belong to many genres. You need to introduce an intermediate table that
> holds the relationship between artist and genre. It could look something
> like this:
>
> artistgenre:
> artist_id INT,
> genre_id INT
>
> Pretty simple table, but it means that you can remove the genre_id from
> your artist table, and manage the relationship between artist and genre
> separately from your actual data. It also makes the "which artists
> belong to this genre" and "which genre does this artist belong to"
> queries really simple. Creating such an association is as simple as
> inserting a record, and removing the relationship is just deleting one.
>
> Phil.

Thanks!
I was afraid it was going to be like this: how would i use this in a
form, to manage the Genres with an artist.
If i'd use tickboxes it would be quite complicated witch the delete and
insert records. Etc.
Can you help me a step further?

Frizzle.

[Frans]

"frizzle" <phpfrizzle@gmail.com> wrote in message
news:1148936045.879502.95350@j73g2000cwa.googlegro ups.com...

> Thanks!
> I was afraid it was going to be like this: how would i use this in a
> form, to manage the Genres with an artist.
> If i'd use tickboxes it would be quite complicated witch the delete and
> insert records. Etc.
> Can you help me a step further?
>
> Frizzle.

Not to be a smartass or anything, but it sounds to me like you would
be well off to read some database tutorials or books to get you started.

[frizzle]

Frans wrote:

> "frizzle" <phpfrizzle@gmail.com> wrote in message
> news:1148936045.879502.95350@j73g2000cwa.googlegro ups.com...

> > Thanks!
> > I was afraid it was going to be like this: how would i use this in a
> > form, to manage the Genres with an artist.
> > If i'd use tickboxes it would be quite complicated witch the delete and
> > insert records. Etc.
> > Can you help me a step further?
> >
> > Frizzle.

>
> Not to be a smartass or anything, but it sounds to me like you would
> be well off to read some database tutorials or books to get you started.

*cough* smartass *cough*

It's not like i don't know _anything_ about these matters, it's just
that sometimes i need a hint of what direction to think of ...

Frizzle.

[Jerry Stuckle]

frizzle wrote:

> Frans wrote:
>

>>"frizzle" <phpfrizzle@gmail.com> wrote in message
>>news:1148936045.879502.95350@j73g2000cwa.googleg roups.com...
>>

>>>Thanks!
>>>I was afraid it was going to be like this: how would i use this in a
>>>form, to manage the Genres with an artist.
>>>If i'd use tickboxes it would be quite complicated witch the delete and
>>>insert records. Etc.
>>>Can you help me a step further?
>>>
>>>Frizzle.

>>
>>Not to be a smartass or anything, but it sounds to me like you would
>>be well off to read some database tutorials or books to get you started.

>
>
> *cough* smartass *cough*
>
> It's not like i don't know _anything_ about these matters, it's just
> that sometimes i need a hint of what direction to think of ...
>
> Frizzle.
>

I don't think Frans was being a smartass. Rather, I think he was trying to be
helpful.

You've asked some very basic database questions (like how to do many-to-many
links) which are answered in about any basic database book. I also think a
little studying on your part would go a long ways towards helping you understand
how to do things.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
[email]jstucklex@attglobal.net[/email]
==================

[frizzle]

Jerry Stuckle wrote:

> frizzle wrote:

> > Frans wrote:
> >

> >>"frizzle" <phpfrizzle@gmail.com> wrote in message
> >>news:1148936045.879502.95350@j73g2000cwa.googleg roups.com...
> >>
> >>>Thanks!
> >>>I was afraid it was going to be like this: how would i use this in a
> >>>form, to manage the Genres with an artist.
> >>>If i'd use tickboxes it would be quite complicated witch the delete and
> >>>insert records. Etc.
> >>>Can you help me a step further?
> >>>
> >>>Frizzle.
> >>
> >>Not to be a smartass or anything, but it sounds to me like you would
> >>be well off to read some database tutorials or books to get you started.

> >
> >
> > *cough* smartass *cough*
> >
> > It's not like i don't know _anything_ about these matters, it's just
> > that sometimes i need a hint of what direction to think of ...
> >
> > Frizzle.
> >

>
> I don't think Frans was being a smartass. Rather, I think he was trying to be
> helpful.
>
> You've asked some very basic database questions (like how to do many-to-many
> links) which are answered in about any basic database book. I also think a
> little studying on your part would go a long ways towards helping you understand
> how to do things.
>
> --
> ==================
> Remove the "x" from my email address
> Jerry Stuckle
> JDS Computer Training Corp.
> [email]jstucklex@attglobal.net[/email]
> ==================

My smartass-response to Frans wasn't intended to be that serieous. It
was only a response to the first part of his reaction (meant as a
joke). Sorry, i guess i'm no comedian.

Anyway, of course, i greatly appreaciate any help i get here, even if
they tell me to read upon things.

Frizzle.