MySQL field with many entries

Question

I have a page listing shows that a band has played, along with all the other bands that played at that show. So far, I've been hand-coding the page, but I plan to move everything over to a table so it's sortable by band/easier to maintain.

As of now, the plan is to have one 'Artists' field, where a typical entry would be ex. 'Britney Spears, Aerosmith, Nicki Minaj.' But I'm wondering if there's a smarter way to do this. Something along the lines of each artist being in its own field? (Keeping in mind that some shows have three bands and some have 100, so actually creating 100 fields isn't feasible.) Or is the current plan sufficient?

Pretty new to this side, so just looking for general opinions/a point in the right direction. Thanks!

Answer 1

Sounds like a many-to-many relationship to me. Try reading up on that.

Answer 2

The idea is to split up your data over several tables instead of having only 1. In your case you would have a table artists and a table shows. The third table would connect these 2 tables and is therefore called a junction table. Simple example:

| show id | artist id |
| 1       | 5         |
| 1       | 7         |
| 2       | 3         |

The IDs are the primary keys of the artists and shows tables.

Answer 3

You need to create 3 tables:

<<Bands>>
 <band_id>, <band_name>, <band_info>
<<Shows>>
 <show_id>, <show_name>, <show_info>
<<Plays>>
 <play_id>, <play_show_id>, <play_band_id>

Fill in all the band information and the show (venue). Then every time a band plays at a show, just enter their codes into the Plays table.