Haven’t done SQL in a long time. I’m talking to a company that puts on events at several different venues–sometimes more than one event at different venues at the same time. For seat reservations, I want to consider that each venue might have different levels (upper, lower, and middle balconies). Each balcony might have different sections with rows and seat numbers.
where that could eventually refer to the actual “seat table”, I’d wind up with a long, long column of, perhaps, hundreds of venue_id in that column.
So, I thought I’d create a join table between the venue, the balcony, the section and row (and eventually the actual seat) but then I’d have a cascade of join tables. Maybe that is the correct way to go but I’m not experienced enough to know if I’m on the right or wrong path.
The eventual goal is to mark the seat as reserved along with customer name, payment info, etc. but I’ve done that part before. This is a sticking point for me.
I’ve seen demo code online for the same thing but it’s often only for one row of seats, not something with this many layers.
What you describe isn’t several many-to-many relationships.
A seat belongs to exactly 1 row. That row belongs to exactly 1 section, that section belongs to exactly 1 balcony, and that balcony belongs to exactly 1 venue.
My original plan was to have separate balcony, section, row tables eventually leading to the individual seat. Are you saying that’s what I should do? But then I’d have that long column of venue ids in the table(s).
That’s a debatable question. Here’s what I would ask if I was designing it:
What are you trying to store information ABOUT?
Does the venue have properties that you want to store, that the balcony does not?
Does the row have properties other than which seats it has and which section it’s in?
If the answer is no, and all I care about is “This seat in a venue is available”, your information is about the seat, and nothing else.