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:matthew@mnc.com.au] Sent: Thursday, January 09, 2003 6:11 PM To: php-db@lists.php.net 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@