Member Avatar for ztwalsh

I am creating a movie database. Right now I have each movie set up as its own table. Inside each table there are fields for title, summary, rating and so on.

On one of my web pages, feeding off this database, I want to show the movie title, summary and rating for every movie in the database. Will this be possible since each movie is it's own table? Can I select all database tables in a query? Should I set up my database differently? Any help will be much appreciated. Thanks.

  • 5 Contributors
  • 4 Replies
  • 85 Views
  • 3 Days Discussion Span
  • Latest Post Latest Post by nav33n

Dani AI

Generated

Good call by and : a table per movie is fragile and hard to query. Storing each movie as a row lets you list, search, sort and aggregate across all movies easily, and it scales. If you already have a bunch of per-movie tables, plan a one-time migration into a normalized schema rather than trying to query every table dynamically.

A practical schema pattern (no SQL shown here) is: a central movies table for core attributes; separate tables for genres and people; mapping tables for many-to-many relations (movie↔genre, movie↔person with role); and a reviews/ratings table for user feedback. Use InnoDB so foreign keys and transactions enforce integrity, add indexes on columns used for joins/filters, and use full-text indexing or an external search engine for synopsis/title search as needed. For normalization theory see Database normalization. For engine, indexing and full-text options see MySQL documentation on InnoDB and full-text search (search the MySQL manual for the current version).

Example query pattern to get title plus average rating and review count:

SELECT m.title,
       COALESCE(AVG(r.score),0) AS avg_rating,
       COUNT(r.id) AS review_count
FROM movies m
LEFT JOIN reviews r ON r.movie_id = m.id
GROUP BY m.id, m.title;

Index reviews.movie_id to keep that fast. Use EXPLAIN to inspect slow joins, avoid pulling unnecessary columns, and consider caching or denormalizing read-heavy aggregates (average rating) updated asynchronously for very large sites.

Recommended Answers

All 4 Replies

Member Avatar for somedude3488

i would set up the database differently. the way you have it organized but hard to deal with once you are trying to get the information.

make a table for movies with an id column so you can reference a movie itself.

make a reviews table with 2 id colums. one to identify the review itself incase you need to delete it and one to show which movie it belongs to.

LIKE THIS:

CREATE TABLE `movies` (
`m_id` INT AUTO_INCREMENT PRIMARY KEY NOT NULL,
`m_name` VARCHAR(100) NOT NULL,
ect... you get the picture
);

CREATE TABLE `reviews` (
`r_id` INT AUTO_INCREMENT PRIMARY KEY NOT NULL,
`r_m_id` INT NOT NULL, --- This is where the movie id goes
then add more fields
);

then to get the reviews for a movie:

$sql = "SELECT * FROM `reviews` WHERE `r_m_id` = " . $mid;
//where mid is the movie id.

hopefully you understand.

Member Avatar for fenixZ

Yes that's right. I absolutely agree with kkeith29

Member Avatar for patelamitbe

Hy, As i read your description my suggestion is that you have to change your database design because your design is looking very tedious for making new table for all the new movies so it is very time consuming when you display this all record in front page.

I am professional in making database design if you want than i make full database design with minimum of amount if you interested than reply me in my email

Thnaks

Member Avatar for nav33n

I totally agree with Kkeith29. Having different tables for each movie is not a good idea. If you plan to implement something, for example, a "database search" for a movie name, would you go through each and every table ?
Anyway, If you want all the tables in the database, then "show tables" would be the query to list all the tables in that database.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.