MySQL field with many entries

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 opi开发者_Python百科nions/a point in the right direction. Thanks!

---

You can start with a simple many-to-many join - see An Introduction to Database Normalization and How to handle a Many-to-Many relationship with PHP and MySQL for a good introduction to these topics.

At it simplest, you have three tables:

1. A table for artists
2. A table for venues
3. A table that links artists to venues

For example:

event <---> event_artist <---> artist

By doing this, you reduce duplication, and make it simpler to produce a range of queries that will allow you to extract useful information from your database. Here's a simple example using MySQL InnoDB tables:

First, create a table which will hold a list of events. Each event gets its own unique ID:

CREATE TABLE `event` (
  `id` INT NOT NULL ,
  `venue` VARCHAR(45) NOT NULL ,
  `date` DATE NOT NULL ,
  PRIMARY KEY (`id`) )
ENGINE = InnoDB;

Now create a table which will hold a list of artists. As with events, each artist is assigned a unique ID:

CREATE TABLE `artist` (
  `id` INT NOT NULL ,
  `name` VARCHAR(45) NOT NULL ,
  PRIMARY KEY (`id`) )
ENGINE = InnoDB;

You now need a table that allows event rows to be linked to artist rows. This is variously called a link table, a junction table, a cross-reference table or an intersection table—I prefer the term junction table.

CREATE  TABLE IF NOT EXISTS `event_artist` (
  `event_id` INT NOT NULL ,
  `artist_id` INT NOT NULL ,
  PRIMARY KEY (`event_id`, `artist_id`) ,
  INDEX `fk_event_artist_event` (`event_id` ASC) ,
  INDEX `fk_event_artist_artist` (`artist_id` ASC) ,
  CONSTRAINT `fk_event_artist_event`
    FOREIGN KEY (`event_id` )
    REFERENCES `event` (`id` )
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_event_artist_artist`
    FOREIGN KEY (`artist_id` )
    REFERENCES `artist` (`id` )
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;

Don't worry about the CONSTRAINT sections just yet—they help maintain database integrity by preventing you from inserting, say, an entry in event_artist for an event that does not exist.

Now, we can insert some data. First, some events:

INSERT INTO `event` (`id`, `venue`, `date`) VALUES
(1, 'Roadhouse', '2010-08-19'),
(2, 'Night & Day Café Music Venue', '2010-08-20'),
(3, 'Night & Day Café Music Venue', '2010-08-25'),
(4, 'The Bridgewater Hall', '2010-09-03');

Now some artists:

INSERT INTO `artist` (`id`, `name`) VALUES
(1, 'Starving Dogs'),
(2, 'The Black and Reds'),
(3, 'Caitlin Rose'),
(4, 'A Little Bite Music - Bella Hardy');

Finally, we can join events and artists:

INSERT INTO `event_artist` (`event_id`, `artist_id`) VALUES
(1, 1), (2, 2), (3, 3), (4, 4);

In this very simple example, the artist with ID 1 is playing at the event with ID 1, artist ID 2 at event ID 2, and so on. As you will see, however, the possibility exists to have multiple artists playing at multiple events.

You now need a way to extract information from these tables. One of the simplest ways to do this is with a JOIN:

SELECT
  event.venue AS event_venue,
  event.date AS event_date,
  artist.name AS artist_name
FROM event_artist
JOIN event ON event.id = event_artist.event_id
JOIN artist ON artist.id = event_artist.artist_id;

+------------------------------+------------+-----------------------------------+
| event_venue                  | event_date | artist_name                       |
+------------------------------+------------+-----------------------------------+
| Roadhouse                    | 2010-08-19 | Starving Dogs                     |
| Night & Day Café Music Venue | 2010-08-20 | The Black and Reds                |
| Night & Day Café Music Venue | 2010-08-25 | Caitlin Rose                      |
| The Bridgewater Hall         | 2010-09-03 | A Little Bite Music - Bella Hardy |
+------------------------------+------------+-----------------------------------+

There are ways in which this example can be improved. You may notice that the event table could easily end up duplicating venue names. You may wish to separate venues off into their own table, so that you have a venue table which holds details of the venue itself (address, telephone, website, etc.), an event table (event date, time, ticket price, etc,), and then the artists table.

UPDATE

You can use MySQL to group the values from multiple rows into a single row. However, this sort of thing is really a view/display issue, and therefore something that would usually be done in your application. Having said that, for simple cases, you can use the MySQL [GROUP_CONCAT][5] function:

SELECT
  event.venue AS event_venue,
  GROUP_CONCAT(artist.name) AS artists
FROM event_artist
JOIN event ON event.id = event_artist.event_id
JOIN artist ON artist.id = event_artist.artist_id
GROUP BY (event.venue);

+------------------------------+-----------------------------------+
| event_venue                  | artists                           |
+------------------------------+-----------------------------------+
| Night & Day Café Music Venue | The Black and Reds,Caitlin Rose   |
| Roadhouse                    | Starving Dogs                     |
| The Bridgewater Hall         | A Little Bite Music - Bella Hardy |
+------------------------------+-----------------------------------+

Note that I have left the date out of the above query. In the sample data given here, the two artists at Night & Day Café appear on different dates. If the date column were included in the above query, MySQL would have to pick one of the two dates, and you would have no control over which one would appear.

---

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.

---

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

---

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.