Re: [PHP-DB] Design suggestions - performance improvement
Matthew Nock wrote: Hi all, I am currently building a site for a cinema to display session times, film synopsis' etc... I have built the database as follows: TABLE: film_detail FilmID FilmName FilmRunTime FilmRating FilmSynopsis etc... TABLE session_data session_ID session_filmID session_StartTime session_Date normalize the SESSION_DATA more. i would: table:film_date fd_id fd_filmid fd_date table: session_data sd_id sd_fd_id (- foreign key) sd_starttime psuedo code: print movie info for (movie) select fd_date from film_date where fd_filmid = filmID print fd_date for (filmID - fd_date) loop select sd_starttime from session_data where fd_id = sd_fd_id print sd_starttime end loop next fd_date next movie film_detail has a 1 to many to film_date film_date has a 1 to many to session_data -- --- Leo G. Divinagracia III [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Design suggestions - performance improvement
What does your SQL look like? I would say make sure you use UNIQUE if that doesn't mess up your implementation ... also, some code snippets would help if you need assistance. Regards, Matthew Moldvan --- System Administrator Trilogy International, Inc http://www.trilogyintl.com/ecommerce/ --- -Original Message- From: Matthew Nock [mailto:[EMAIL PROTECTED]] Sent: Thursday, January 09, 2003 6:11 PM To: [EMAIL PROTECTED] Subject: [PHP-DB] Design suggestions - performance improvement Hi all, I am currently building a site for a cinema to display session times, film synopsis' etc... I have built the database as follows: TABLE: film_detail FilmID FilmName FilmRunTime FilmRating FilmSynopsis etc... TABLE session_data session_ID session_filmID session_StartTime session_Date The session_data table might contain a large number of records with the same film ID, and the same Start Date to cover a large number of sessions that we would have of the same film on the same day. I want to know what is the best way to retrieve this joint info from the DB. currently, I run a single select statement requesting the fields I want from both tables, where the session_Date equals a given date. This returns x number of rows, depending on the number of sessions for all films for that given day. However, this means that I am retrieving the Film Synopsis, cast, runtime etc multiple times... The data will be returned to the user as below: FILM TITLE (rating) session_times--- will list all sessions - such as 8.45am, 10.15am, 12.00pm, 2.00pm etc etc FILM RUNTIME FILM CAST FILM SYNOPSIS is this an effective way to return to he data? or should I be using individual queries? Any comments or suggestions would be most appreciated.. Cheers, M@ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Design suggestions - performance improvement
Hi all, I am currently building a site for a cinema to display session times, film synopsis' etc... I have built the database as follows: TABLE: film_detail FilmID FilmName FilmRunTime FilmRating FilmSynopsis etc... TABLE session_data session_ID session_filmID session_StartTime session_Date The session_data table might contain a large number of records with the same film ID, and the same Start Date to cover a large number of sessions that we would have of the same film on the same day. I want to know what is the best way to retrieve this joint info from the DB. currently, I run a single select statement requesting the fields I want from both tables, where the session_Date equals a given date. This returns x number of rows, depending on the number of sessions for all films for that given day. However, this means that I am retrieving the Film Synopsis, cast, runtime etc multiple times... The data will be returned to the user as below: FILM TITLE (rating) session_times--- will list all sessions - such as 8.45am, 10.15am, 12.00pm, 2.00pm etc etc FILM RUNTIME FILM CAST FILM SYNOPSIS is this an effective way to return to he data? or should I be using individual queries? Any comments or suggestions would be most appreciated.. Cheers, M@
