Feedback on Data model for Cinema ticket booking system

[webdeveloper123]

Hey Guys,

Just looking for some feedback/critique on this data model. It's for a cinema booking system. Main thing I'm concerned about is that (I think) I won't be able to reuse seats. So I'm worried I haven't got my data model correct in the sense that I can reuse seats across different movies/days/screenings. (But I may have, but I have a lingering doubt) So I got this feeling in the seat entity:

Seat Id        ScreenID       Row      Number     Status

1                    4                   A             1               booked

So say ScreenId 4 relates to screen number 4, and seat A1 gets booked, is that it - has A1 gone forever or am I able to release the seat for the next showing in the same screen id

Other options for Status are: Available and reserved.

Or is it just a simple case of changing the status from booked to available after the movie finishes?
Any feedback would be welcome.

Thanks

Edited January 2 by webdeveloper123

[requinix]

It's not possible to book a particular seat in the theater. (Most likely.) Instead, one has to book a particular seat in the theater for a particular screening. Thus the status must be associated with something that has both of those pieces of data. You don't have such an entity... basically.

You could build one by creating a "screening_seat" table, consisting of a screening_id and seat_id. Rows could be created on-demand, removing the need to pre-populate that table when a screening is created. You would then adapt the booking_details to reference that screening_seat instead.

However, if the status is a simple boolean "booked / not booked" then you don't need to store it because it can be inferred from the existence of a booking for a seat. In other words, with the current design, if there is a booking_details with seat_id = $seat and booking.screening_id = $screening then the seat is considered booked, and if not then the seat is considered free.

[Barand]

[webdeveloper123]

15 hours ago, requinix said:

, one has to book a particular seat in the theater for a particular screening. 

Yes, that Is what I was after.

15 hours ago, requinix said:

You could build one by creating a "screening_seat" table

That's a good idea. I was thinking originally to put screening_id in the seat table, but didn't think it made much sense. Better to create a new entity.

 

15 hours ago, requinix said:

Rows could be created on-demand

Yes, that was the job of rows and columns in the screen entity

16 hours ago, requinix said:

removing the need to pre-populate that table when a screening is created

You mean don't fill seat entity with all seats manually. eg A-1, A-2 etc? Different screens will be different sizes. What would I do instead?

 

16 hours ago, requinix said:

However, if the status is a simple boolean "booked / not booked" then you don't need to store it

Got you.

@Barand Thanks for the data model. I too was thinking of splitting movie into 2 entities with the other being genre. I will digest this and post back later.

Thanks guys!

[gizmola]

I suggest using the model that Barand supplied.

For the booking table, you create a unique index on screening_id, seat_id, and at that point you are protected from double booking for a screening.  You can easily query for tickets sold for a screening, and can also determine what seats are available with a query like this:

(Note, you constrain this query, knowing in advance the screen_id (I used 1 here) and the screening_id (used 35).  In php these values would be fed into the query as parameters, but this is just an example:

 

SELECT s.id, s.row, s.seat_no
FROM seat s  
LEFT JOIN booking b
ON b.seat_id = s.id AND b.screening_id = 35
WHERE s.screen_id = 1 AND b.id IS NULL

 

[Barand]

@gizmola With my model you would need to bring the ticket date into the equation

SELECT s.id, s.row, s.seat_no
FROM seat s  
LEFT JOIN booking b
ON b.seat_id = s.id 
   AND b.screening_id = 35 
   AND B.ticket_date = '2024-01-03'                      -- also required
WHERE s.screen_id = 1 
      AND b.id IS NULL;

As it is, a screening record states that the movie will be screened at time T each day between X and Y.

On reflection, although requiring more rows, it would be better to have a screening record for every individual screening, giving...

then your query would work as it is.

Alternatively, to get vacant seats for a screening (and only requiring the screening id as input - if the screening id is known then the screen id is also known) you could

SELECT s.id, s.row, s.seat_no
FROM screening sg 
     JOIN seat s ON sg.screen_id = s.screen_id
     LEFT JOIN booking b ON b.screening_id = sg.id 
                         AND b.seat_id = s.id
WHERE sg.id = 35 
      AND b.id IS NULL

 

[webdeveloper123]

Hey Guys,

Sorry for the late reply. I think I will take advice from here and go with Barand's data model. I'll post back later with ERD. 

[webdeveloper123]

Hey Barand,

Thanks for all your help. Sorry, just for clarity, is this the final data model?

Also, For the booking table, create a unique index on screening_id, seat_id, and at that point I am protected from double booking for a screening. 

Edited January 5 by webdeveloper123

[Barand]

Screen.rows, screen.columns, screen.capacity look like derived values to me - you can get that info from the seats for each screen.

booking.ticket_date is no longer required - duplicates the screening.screen_on value.

However, you need to consider the payment process. What if a user books seats and their payment is subsequntly declined?

You would need to know who made the booking. Perhaps make the booking.ticket date an automatic timestamp and, on booking, set a status "payment pending". Set status to "paid" on successful payment. Periodically remove pending payments older than X minutes to release the seats for sale again.

[webdeveloper123]

On 1/5/2024 at 7:26 PM, Barand said:

Screen.rows, screen.columns, screen.capacity look like derived values to me - you can get that info from the seats for each screen.

 

The thing is I have some JS in conjunction with some php that generates the cinema screen map on the fly based on rows and columns. So 6 Rows, it does it alphabetically so it would be rows A to F, and then say 10 columns would be 10 seats in the row. So You would get A1, A2 etc all the way up to A-10, for each row etc.  Then that same screen map you just select the seats you want then press book, and it enters it into the DB.

Here is a screen shot:

A few things going on here. The red seats are unavailable because they have already been booked, the blue seats are the ones I have just selected and then I press "Book selected Seats" then I get a JS alert saying "seats booked" then when I ok that, the blue seats turn red.

Here is a snippet of code:

    $primaryKeyValue = 5;



  $stmt = $conn->prepare("SELECT row AS numRows, numbercols AS numCols FROM seats WHERE seating_id = ?");
    $stmt->execute([$primaryKeyValue]);
    $seatMapData = $stmt->fetch(PDO::FETCH_ASSOC);

Obviously this is a stand alone example and it will require further code to integrate it into the system. $seatMapData then goes into the JS and it builds the map as show on screen shot. I am sort of modeling my system on the Vue cinema chain (myvue.com) If you go there and mess around with the website you'll get an idea of what I am trying to do.

On 1/5/2024 at 7:26 PM, Barand said:

booking.ticket_date is no longer required - duplicates the screening.screen_on value.

 

Got it.

On 1/5/2024 at 7:26 PM, Barand said:

However, you need to consider the payment process

I'll be honest with you I wanted to integrate a Payment API into this project. So I went off to Stripe API & signed up and got my API keys and I was going to run it in Test Mode. I did the webhooks successfully and thought I was doing well. But then when I tried to simulate a transaction -  I just didn't get it. Maybe I have to get some more programming experience, go up a notch of 2 then do it. It was all in OOP (which is ok because I learnt that in Uni) but haven't revisited it since. I know you can use the Stripe API procedurally, but it looks very complicated. So with the "book selected seats", I am just going to Pretend a transaction took place and let the booking go through. 

Thanks Barand

[webdeveloper123]

On 1/5/2024 at 7:26 PM, Barand said:

you can get that info from the seats for each screen.

Ahh I think I get what you mean. Just use row and seat_number from seat instead

[webdeveloper123]

Hey guys,

I went with screening.screen_on in my data model as per Barands advice. Thing is, If I create a record for screening entity, I would have to put the date that movie is being screened on. So, say I put todays date, by tomorrow It won't be showing in cinema anymore, unless I Create a new record with tomorrows date. Now I know it's going to take me more than 1 day to finish this project, so everyday I have to load new data in. Would it better if I went for screen_from to screen_until that way I could create a long date range and not have to load new data everyday? Or should I do something like:

 

if (screen_on < 2024/12/31) 
display listings
else
dont display
                       

and then when I finish the code and everything is ok, change it to:

if (screen_on == Today) 
display listings
else
dont display
                       

Thanks

Edited January 15 by webdeveloper123

[webdeveloper123]

Would it be a good idea to make a Unique Constraint so I can't double book screenings? So for example do it so that I can't show different movies in the same screen at the same time on the same day?

[gizmola]

It is always a good idea to enforce your business rules with constraints, whenever you are clear.

As for loading in data for a system you've developed for an assignment, just generate a bunch of screening rows.  

This type of seeding of data is fairly typical, and there are various libraries like faker (https://fakerphp.github.io/) that can be used for generating test data which are often used by developers.

[webdeveloper123]

17 hours ago, gizmola said:

just generate a bunch of screening rows.  

Do you mean write a script that will generate it for me?

[webdeveloper123]

17 hours ago, gizmola said:

It is always a good idea to enforce your business rules with constraints, whenever you are clear.

 

Would the unique constraint be on: screen_on, screen_at and screen_id ?

[Barand]

56 minutes ago, webdeveloper123 said:

Would the unique constraint be on: screen_on, screen_at and screen_id ?

Yes

1 hour ago, webdeveloper123 said:

Do you mean write a script that will generate it for me?

Do you not already have a form for the user to add movies and screenings?

[webdeveloper123]

2 minutes ago, Barand said:

Yes

Thanks

2 minutes ago, Barand said:

Do you not already have a form for the user to add movies and screenings?

No, I didn't really want to do that, I've practiced lots on INSERT queries forms. I just load the data straight into the database. I've done it now. To change the date I got a little work around: Copy and paste all the data into a new file, and use find and replace all on the date!

[webdeveloper123]

Hey Barand,

I just realised - I don't have price anywhere. Should I add it?

[Barand]

Good idea!.

The question is where? What is your pricing policy?

[webdeveloper123]

Flat rate, all year round, same price. In that case, would it go in movie table?

[Barand]

The price is not a property of a movie entity, it is dependent on the year (or date period depending on whether the change date is alway jan 1st or not)

Have a price table

+-------------------+
| price             |
--------------------+
| year YEAR (PK)    |
| price DECIMAL(6,2)|
+-------------------+

But if it changes during the year

+-------------------+
| price             |
--------------------+
| id INT (PK)       |
| from DATE         |
| until DATE        |
| price DECIMAL(6,2)|
+-------------------+

Then join to the price table to get the price
 

SELECT price
FROM screening s
     JOIN 
     price p ON s.screen_on BETWEEN p.from AND p.until
          

or

SELECT price
FROM screening s
     JOIN 
     price p ON YEAR(s.screen_on) = p.year

depending on the change date

[webdeveloper123]

hmm....I think I might go for if the price changes during the year. Because in that Hotel project, I set the same price all year round. Seems like a natural step up

Thanks!

[gizmola]

3 hours ago, webdeveloper123 said:

Do you mean write a script that will generate it for me?

Yes exactly. 

The better known frameworks (symfony & laravel) come with cli libraries, but there are also many standalone libraries out there that take care of a lot of cli details, like providing argument handling.  For example:  https://splitbrain.github.io/php-cli/

You don't need a cli library, but they can save you a lot of time.  At most rudimentary however, you can run a script using the cli,  with php -f. 

[webdeveloper123]

Thanks gizmola