Is the database decomposition correct?

Question

MySQL

There is a database for a stadium or for some other event where it is possible to buy a ticket or just reserve a seat, it does not matter yet. The goal is to start by simply looking at whether a seat is booked or not. It is believed that there is a sector, a row, and of course the place itself. At first, I did this, created a sector table, a row table and a seat table that stored the sector and row id, and the user table, in addition to the contact data fields, also had the reserved(bool) and seat_id fields (the id of the seat table with other IDs) and more field for the number of the seat itself. But it seemed to me that it was necessary and could be done much easier, so I settled on this option:
User(table):
id int PK,
name varchar,
surname varchar
Seat(table)
id int PK,
row int,
number int,
sector enum('A', 'B', 'C')
Reservation(table)
id int PK,
row_id int FK,
seat_id int FK,
reserved bool
In general which approach is best first or second, if no, please tell me the correct one. It is believed that there is a predetermined number of places, rows and sectors. 5 rows, 3 sectors, and 8 seats in 1 row. Thank you in advance.

Answer 1

In general, everything is quite simple for you. I will offer you a variant of the scheme, and you compare it with your own.
Let's try to think in terms of sets - this is always useful when designing relational databases, because relations are sets.
So, you have a variety of places available for booking. We will assume that the database serves only ONE stadium. The sector, row and place number are all hierarchical coordinates of the place, uniquely defining this place. So the key for the place is (sector, row, number).
Next, we need to decide how we will represent the set of ALL places. You indicated that the number of seats is known in advance. In theory, in this case, we do not even need to store many of these places, because it is determined by the rule, but sooner or later you will come across the fact that the rule must be broken - for example, some of the seats at the moment turned out to be unsuitable for selling tickets (a chair was broken, a threshold fell off, etc.). Therefore, IMHO, it is still worth creating a "Place" relation to store a set of ALL available places, as you actually did. Those. we have the first relation: Seat(sector, row, number).
Now we want to store a lot of reserved seats. Because there will be many events at the stadium, for each of the events we will have our own set of reserved seats. This means that the primary key of the event must fall into the primary key of the "reserved seat" relationship. Let's assume that the event's primary key is id (because you didn't provide any other details). The remaining attributes of the reserved seat relationship must refer to one of the available seats, i.e. we must have a foreign key in the Seat relation. So we have a second relation: ReservedSeat(event_id, sector, row, number). When designing a relational database, it is very important to clearly understand what the PRESENCE or ABSENCE of a record in each of the relations means. The presence of an entry in relation to ReservedSeat tells usevent. No more, no less. If there is no entry in relation to ReservedSeat, then a specific seat for a specific event is still free .
And yes, it seems we have forgotten the main thing - and who is the place occupied by? We need one more attribute, a foreign key on the User relation. Let's add it:

ReservedSeat(event_id, sector, row, number, user_id)

. It is important that this attribute is not included in the primary key, because different customers cannot book the same seat (i.e. the seat is always booked by the same person).
Actually, everything. We rewrite the User relation as is, because you didn't give details about what to store there. I will come up with the Event relation "from the ceiling", adding only the name attribute there in addition to the id key.
Total (attributes included in the primary key are in bold):
User( id , name, surname);
Seat( sector , row , number );
Event( id , name);
ReservedSeat( event_id , sector ,row , number , user_id); foreign keys: event_id -> Event(id), user_id -> User(id); (sector, row, number) -> Seat(sector, row, number).
I hope the decision-making process is described clearly enough. If you have any questions - ask.

Answer 2

I would do this:
there is a set of places that are somehow characterized there (let it be a sector, a row and a place - it doesn’t really matter) and each such entry has its own ID:

[Seat]
id
sector
row
number

as far as I understand, places are bought/reserved for some event (match, concert, etc.). accordingly, there should be a table of these events. For simplicity, we characterize it only by the date. we don’t even need a date, but let it be

[Event]
id
datetime

the seat reservation or purchase must refer to the Seat table and the Event table:

[Reservation]
id
seat_id
event_id
// another fields

user can be inserted here, but I would separate the connection with the user into separate entities: Ticket as a ticket and another entity as the fact of selling a ticket to a certain user TicketTransfer . but it already depends on your service that you implement,
I think that it will be right

Answer 3

I myself often use enum, but you need to understand their limitations / features and not sculpt thoughtlessly. I recommend reading the book SQL Antipatterns . There's some mixed advice in there, but at least it gives food for thought.
Probably, nevertheless, I would make separate tables for sectors and rows of a sector, because now it is considered that there are so many sectors, rows and places, and then suddenly it will be considered differently . And in the table of places would refer only to the necessary row of the sector.
The fact that you took out the armor from the users is absolutely correct, they do not belong there.
But the armor table is strange:

• Why is there a boolean field if it's armor anyway? Maybe an unbooked reservation?
  
• Where is the link to the booked user? It can be in the armor table or in a separate table, but you can’t do without it.
  
• Why refer to both a place and a row if the place is already tied to the row? Moreover, in the table of places, the row is just int, and in the armors there is also a foreign key.
  
• Fortunately, seats are reserved for some events, not forever, so for the sake of completeness, you need to add them.
  

Answer 4

It's a matter of time. Or do you plan to put a reserve for each place once?