-2

I'm making a database of concerts that I've attended in SQLite. I have the following tables setup:

  1. Venues
  2. Artists
  3. Concerts

How can I make it so the concerts table can have multiple artist IDs associated with it? Should there just be multiple columns for ArtistId1, ArtistId2, ArtistId3, etc.?

Or, will I need a 3rd table that lists each ArtistId and the corresponding ConcertId?

I've tried looking up ways to put multiple artist ID's in the same column delimited somehow, but not sure that's an option.

eric_1487
  • 3
  • 1
  • Does this answer your question? [How to implement one-to-one, one-to-many and many-to-many relationships while designing tables?](https://stackoverflow.com/questions/7296846/how-to-implement-one-to-one-one-to-many-and-many-to-many-relationships-while-de) – user4157124 Jul 29 '23 at 21:58

1 Answers1

-1

When you have a many-to-many relationship (a concert can have many artists, and an artist can perform at many concerts), you need an associative entity, which at a minimum has foreign keys to both tables:

create table concert_artists (
    concert_id int not null references concerts,
    artist_id int not null references artists
)

Such tables can also store information about the relationship, for example you might add the performance number (1 for the first act, and so on) of the performance and how many minutes the artist performs:

create table concert_artists (
    concert_id int not null references concerts,
    artist_id int not null references artists,
    performance_number int,
    performance_duration_minutes int
)

Side note: It's best practice to name table names in the singular, so venue, artist and concert (not the plural as you have named them).

Bohemian
  • 412,405
  • 93
  • 575
  • 722