I have events using different venues for seating.
They are saved in 3 different tables.
Venues (has information about the venue like name, location, capacity etc)
id | name | location | capacity
Venue_chart (has information about seating chart with rows and column)
id | venue_id | seat_type | row | column | label
seat_type
id | name | css_color | description
Now when an event is created as of now I save those data into a table with one to one relation with events table
event_venues
id | event_id | venue_id | some extra columns
This works fine when an event wants to change the venue completely if the venue is not available or last minute change, I only delete entry in event_venues row and insert 1 new row.
The issue is on the venues table side. How do I handle changes in the venue seating map? How do I make sure that the changes in venue seating map will only apply to new events and not affect existing or past events.
In short if I make change in venue seating map today then, all new events using the venue will see changes but the existing and old one should see the old seating map?
I thought of creating a new table called event_venue_chart and copy all rows (which could be 300 per venue) from venue_chart table. That way any new changes will not affect existing or old events. I realised that it will create many duplicate rows will only change in eventId.
Thank you