frizzle wrote:This is a fairly classic problem: how to represent a many-to-many> 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.
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
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.