0

I am learning SQL and I created this database to offer a cinema reservation service.

cinema booking service

My problem is that I can't make a reservation because my reservation table doesn't have a foreign key to table room. I also can't create a relation from reservation to table room because that would violate the normalization rules.

An reservation without a reserved seat doesn't make sense. Can you give me an advice how I can solve that?

I am using MySQL.

Umut Savas
  • 219
  • 3
  • 19
  • "I can't make a reservation because my reservation table doesn't have a foreign key to table room". So create the foreign key (ie the room). It doesn't make sense to create a reservation if the room doesn't exists. – Eric Mar 11 '19 at 20:45
  • Wouldn't that violate the normalization rules? – Umut Savas Mar 11 '19 at 20:47
  • It's the same as the manual process would have to be. Enter a new room into the table. Make a reservation using that room. You can't use the room before it exists in the correct table. – Hopper Mar 11 '19 at 20:54
  • Can you post a sample of what you're trying to insert. – Eric Mar 11 '19 at 20:54

1 Answers1

2

You have to do your inserts in sequence. Create the lowest level entry first then ascend the sequence.

If I need a room to make a reservation then I create the room before I create the reservation. If you need to pull the identity value of the row that you just created so that you can pass it along the process then you can use LAST_INSERT_ID(). The equivalent of SQLServer function SCOPE_IDENTITY() in mySQL?

As an example of passing a key along: 

Declare PassedKey Integer; 

INSERT INTO TableRoom values (...) 
SET PassedKey = LAST_INSERT_ID()

INSERT INTO TableReservation values (PassedKey, ...)
Hopper
  • 146
  • 6