Many to Many ID to Many to Many Table

[Ruegen]

I have a table I use for show years



ID
ShowID
YearID

right now I have primary keys on ShowID & YearID as this is a many to many table

I now have another many to many table that stores multiple performers per show year

so I make a show year, add the year id so and add mutiple performers. This works but I wanted to create referential integrity.

The performer junction table is empty - blank slate.

I am adding the ID field of the show year junction table to a field called showyearjncID

the performer junction table is as so

ID
PerformerID (key)
ShowYearJncID (key)

when I try adding the show year junction table ID (ID) to showyearjncid I can't get referential integrity to work - I get a no unique index error

how to fix?

[orange]

I think you mean many to many relationship between tables which is resolved by creating a Junction table, and 2 1 to many relationships.

I'm not sure what your tables are specifically.

I see
Show and Performer, and since 1 show could have many performers and 1 perfomrer could participate in many shows, the relationship would seem to be Many to Many.

Show<----->Performer and to resolve this

add a junction table eg PerformerAtShow

You could have a field such as PerformerAtShow_ID( a surrogate) which I called PAS_ID as PK, and a
unique composite index on the PerformerID and ShowID to prevent duplicates.

Or you could have a compound primary key using PerformerID and ShowID.

This is based on my understanding of your post, which may not be what you meant.

[Ruegen]

I think you mean many to many relationship between tables which is resolved by creating a Junction table, and 2 1 to many relationships.

I'm not sure what your tables are specifically.

I see
Show and Performer, and since 1 show could have many performers and 1 perfomrer could participate in many shows, the relationship would seem to be Many to Many.

Show<----->Performer and to resolve this

add a junction table eg PerformerAtShow

You could have a field such as PerformerAtShow_ID( a surrogate) which I called PAS_ID as PK, and a
unique composite index on the PerformerID and ShowID to prevent duplicates.

Or you could have a compound primary key using PerformerID and ShowID.

This is based on my understanding of your post, which may not be what you meant.

I had that before hand however now I want to have multiple show years per show and multiple performers for each year for each show....

[Ruegen]

I've screen shot my backend - you should be able to see there is no integrity in the link

[Ruegen]

I whipped up a small database to see if I could replicated if there was no data entered - same result. Can't attach a many to many table to a many to many table..... and have referential integrity..

[orange]

Please tell readers, or show us by example, what you mean by many to many table.
The model I posted will allow many shows per year and many performers per show.
There must be more detail in your set up than you have described.

What exactly do you mean by YearChosen? I don't understand why this would be in a table.

With the set up I showed, you can find all Shows for a given year by ShowDate.
All Performers in a Show by means of PerformerID in PerformersAtShow and All Shows that a performer participated in.

[Ruegen]

Please tell readers, or show us by example, what you mean by many to many table.
The model I posted will allow many shows per year and many performers per show.
There must be more detail in your set up than you have described.

What exactly do you mean by YearChosen? I don't understand why this would be in a table.

With the set up I showed, you can find all Shows for a given year by ShowDate.
All Performers in a Show by means of PerformerID in PerformersAtShow and All Shows that a performer participated in.

I keep a years table - I add a year (2016, 2017 etc) so that I can add a yearID in a many to many table as well as a showID in that table

That way I can have many shows for the same year - so for 2016 I have shows, 1,2 and 4 while for 2015 I have shows 1,2 and 5.

This way they are the same show every year but divided by the year. Each show year is also granted many performers

I can't grant one show year only one performer - I require many performers for each year of that show.

So for example

a show called "The Circus"

is touring 2015 & 2016

however in 2016 I have two performers and not one.

This is why I created the new table to junction the performers to the show year.

[Ruegen]

to add:

We keep a very large list of shows available. Some years they tour, some years they don't. Hence why I made a table for years rather than using a datefield. Some cases a show might come back and so I add a row in the junction table - which will contain the showID and the yearID

[orange]

What exactly does the YearsTable give you that you can't get by doing a simple query against the ShowDate within the ShowTable ?

[Ruegen]

What exactly does the YearsTable give you that you can't get by doing a simple query against the ShowDate within the ShowTable ?

The ability to give different prices for years or in another case - have a combo box that lets you select year then select show within that year (in some cases it might have the same show for both available years).

Staff have to make bookings and when they create a new one they have to choose the year and show to go with the year because the prices differ.

[orange]

As I said in post 6

There must be more detail in your set up than you have described.

and it appears there is.

[Ruegen]

As I said in post 6


and it appears there is.

Yeah sorry but I was mainly focused on the DB table design. So can you help me doc? Is it terminal?

[Ruegen]

dump* cough*

[orange]

I was trying to suggest that more info about your business and rules would help put your post into context.
Here is a generic data model that "may" be along the line of what your business entails. The model is meant as a guide identifying some of the entities and attributes normally found in the "entertainment business", but it is a guide only and may not reflect your situation in detail. You can use or exclude any pieces that you consider "outside your scope", and add and/or adjust to meet your needs.

Good luck.

[Ruegen]

I was trying to suggest that more info about your business and rules would help put your post into context.
Here is a generic data model that "may" be along the line of what your business entails. The model is meant as a guide identifying some of the entities and attributes normally found in the "entertainment business", but it is a guide only and may not reflect your situation in detail. You can use or exclude any pieces that you consider "outside your scope", and add and/or adjust to meet your needs.

Good luck.

I guess I could always make a performer table that stores YearID AND ShowID as well as PerformerID and have a subform that only shows performers that match the yearID & showID (the query is filtered to match showID and YearID with no control source)

I'll keep the db diagram though for future use - in other projects it will come in handy. Thanks