Feedback on Data model for Cinema ticket booking system

[webdeveloper123]

Hey Guys,

I think it's going to be better If I have the screening on fixed days. So for instance I have a nav bar that has all the days of the week - So when I click on Monday, it will always show me the same listings, same with Tuesday etc. This way if I want to show someone my website in a months time, I don't have to load new data everytime. This will mean a change to the data model. 

Shall I change screening.screen_on to a varchar and have "Monday" for all of Mondays movies, and "Tuesday" for all of Tuesdays movies or shall I have a seperate entity for the days of the week and do a JOIN.

Thanks

[Barand]

If you know the date, you know the day of the week.

For example

SELECT m.title
     , s.name as screen
     , date_format(sg.screen_on, '%W, %D %b') as date
FROM screening sg
     JOIN screen s ON sg.screen_id = s.id
     JOIN movie m ON sg.movie_id - m.id
WHERE dayname(screen_on) = 'Wednesday'
ORDER BY screen_on;

giving

+------------------------+------------+---------------------+
| title                  | screen     | date                |
+------------------------+------------+---------------------+
| Mission Implausible    | Screen One | Wednesday, 17th Jan |
| Shaving Ryan's Private | Screen Two | Wednesday, 17th Jan |
+------------------------+------------+---------------------+

 

[webdeveloper123]

Thanks Barand, I'll give it a go

[webdeveloper123]

haha yes! Thanks Barand. I added to the code and I got there in the end!