Database for Movie ShowTime

Question

I create one database that is theatre. In that Movie,Cinemas tables are created, but now i want to create showtime table in my database so i have confusion that how to make that table,how many fields are put in that table and how to store time of show in that.I want to put show time with price that is silver, gold, and platinum.

If there is any idea then tell me.

Thanks.

Answer 1

Well, in showtime you basically should have the columns

stmid int,              -- pointing to entry in movie table
stcid int,              -- pointing to entry in cinema table
ststart datetime,       -- the actual "showtime"
stprice float           -- ticket price

And then define stmid,stcid to be the Primary Key like

PRIMARY KEY (stmid,stcid)

I would not assign silver, gold or platinum directly in the table but leave that to a later stage, either in a QUERY or a VIEW, depending on criteria that might change over time. The price on the other hand is a very concrete and fixed data type.

2. Edit:

To select the movies that will play within the next 24h you could use the following:

SELECT mname,
       GROUP_CONCAT(DISTINCT DATE_FORMAT(ststart,'%l:%i %p') ORDER BY ststart) mtimes 
FROM movies 
INNER JOIN showtime ON stmid=mid   
WHERE mname LIKE '%name of movie%' AND ststart BETWEEN NOW() AND ADDDATE(NOW(),1)
GROUP BY mname

This statements assumes that there are columns mid (unique movie ID) and mname (movie name) in table movies.