better database design for nested relation (many-to-many-to-many?)

Question

I am a student learned about database theoretically, not as practice.

But now I have a chance to design database while doing this little team toy-project.

Here is a diagram (omitted 1:1, 1:n, n:m) as below : enter image description here

And Here is description about tables :

1. Theater

This table contains informations about theater. A theater can have many shows.

2. Seat

This table contains informations about seat.

The seat can be distinguished by Primary key pair : (theater_no, seat_no)

3. Show

This table contains informations about show.

A theater can have many shows. But the show will take place in one theater only.

(So Theater depends on Show. we can take proper Theater automatically after selecting Show.)

4. seat_show_grade

This table contains seat-grades for each seat. This is a mapping table between 'seat' and 'show' because a same seat can be different grade for different show.

[Here is an example : ]

'Show 1', 'Show 2' take place in same Theater, 'Theater 1' and 'Theater 1' have 'Seat 1'

Then, 'Seat 1' is A-grade for 'Show 1' and 'Seat 1' is C-grade for 'Show 2'.

5. Users

This table contains informations about user.

6. reviews

This table contains informations about reviews.

7. seat_review

This table contains informations about reviews for seat.

8. show_review

This table contains informations about reviews for show.

---

Service Logic :

1. User logins App, a main page lists shows.

2. If user selects a show, lists seat of theater (where chosen show took place in).

3. If user selects a seat, shows review about seat (but review also depends on show).

---

The '3' is a main problem for me, because it is hard to design 'reviews' table since it depends not only show but also seat.

At first, I designed 'reviews' table with 4 Primary key : (user_id, show_no, theater_no, seat_no) without 'seat_review' table and 'show_review' table.

But I thought this is too messy, so I changed design like above.

I am still confused about this design, I can not sure this preserve data integrity, processing speed, etc.

---

So, my main question is :

1. Is there any nice idea to design 'review' table? (it seems (many-to-many)-to-many (?) for me.)

2. For left 4 tables ('theater', 'seat', 'seat_show_grade', 'show') has cyclic relation. Is this bad design for database? If it is bad, how can I change this one?

Thanks for your help.

---

Appended :

The past idea with using mapping table.

(What I said 4 primary keys, no 'seat_review' and 'show_review')

enter image description here

• Rick James: A 4-column Primary key : (user_id, show_no, theater_no, seat_no) is not only possible, but seem "right" for the application. If you need to group reviews by "seat_grade`, then do a JOIN.
• bFur4list: @RickJames Thanks for your response! Do you mean a 4 Primary key option is a second diagram what I appended below, right?
• Rick James: Let's see the SELECTs that you will use against this database. That exercise will yelp you see whether the schema is "good".

Answer 1

Is there any nice idea to design 'review' table? (it seems (many-to-many)-to-many (?) for me.)

The seat_review table you show is not many-to-many-to-many. It's simply representing one many-to-many relationship between reviews and seat.

It might seem that way because it has three columns, but one of the tables it references has a composite primary key, so the foreign key that references that primary key must also have multiple columns.

You have a second many-to-many relationship between reviews and show. This is independent of the first many-to-many relationship between reviews and seat.

Trying to combine independent many-to-many relationships into a single intersection table is a violation of 4th Normal Form. Don't do it! It causes more trouble than it saves.

For left 4 tables ('theater', 'seat', 'seat_show_grade', 'show') has cyclic relation. Is this bad design for database? If it is bad, how can I change this one?

I think you mean the right 4 tables. At least, those four tables are on the right as I look at your diagram.

They do not have a cyclic relationship.

• seat_show_grade references seat and show
• seat references theater
• show references theater

There's no reference from theater to any of the other tables. Thus no cycle.

• bFur4list: Great approciate for your response! I understand about my second question. For your response about my first question, does "Trying to combine independent many-to-many relationships into a single intersection table is a violation of 4th Normal Form. Don't do it! It causes more trouble than it saves." mean that a second diagram (I appended below) is worse than first diagram?
• Bill Karwin: Well, the second diagram loses some information. If you want a review for a show to be distinct from a review of a specific seat, your diagram doesn't have that. But if you only need reviews of specific seats at specific shows, it's okay. I don't know what information you need.
• bFur4list: First, I am sorry for my bad English. This project provides review of seat (but review also depends on show). For example, two different show 'Show A', 'Show B' take place in same 'Theater 1' and 'Theater 1' has 'Seat 1'. Then if user selects 'Show A' and 'Seat 1', there will be a review of 'Seat 1' about 'Show A'. If selects 'Show B' and 'Seat 1' there will be a review of 'Seat 1' about 'Show B'. I'm not sure if my intention was conveyed properly.
• bFur4list: So, In summary, I don't need information about just shows, but I need information about seat, which varies with show.
• Bill Karwin: Then your second diagram is good.

Answer 2

Listen, I've been working in a company that was providing services for cinemas all over the world and your design is wrong.

You should have a star schema like this:

enter image description here

At the center of the star schema there should be a table called Ticket or Transaction because your whole business rotate around that. Not Seats or Shows.

Plus you are voluntary creating loops: see how your dimensions are creating circles? That will cause blocking and deadlock all over the place. This is a wrong approach: those tables shouldn't be linked between themselves but they should be directly linked o a fact table (Ticket or Transaction).

• bFur4list: Thanks for your response, But I have a question in this design : how should I handle 'seat_grade'? it depends not only seat but also show.