Hello, things are looking good. Now I have a question about how to handle editions for movies. There is a lot of information that might change between one edition and another, all these fields for example:
title, features, image, duration, release, rated, sound, language, subtitles, region, media, # of discs, format, aspect ratio, price, etc.
So I need to put them in another table. My question is, what would be the best way to handle this? It seems that right now, the fields that remain the same are less than the fields that change. Also, the default edition (for lack of a better term) will also need to have an entry in the “edition” for the info stored there.
So I’ll end up with a table that is the main table (dvdpedia) but has little info and an edition table that has most of the info with multiple entries for each title in the main table.
Does that sound ok? It doesn’t “feel” right to me, I can’t help but wonder if I’m missing something.
Here’s my query so far:
SELECT dvdpedia.id
, dvdpedia.origTitle AS 'Original title'
, rated.rated
, w.writers
, a.cast
, studios.studio
, aspectRatio.aspect
, videoFormat.format
, l.languages
, country.country
FROM dvdpedia
LEFT OUTER JOIN edition ON edition.titleId = dvdpedia.id
LEFT OUTER JOIN rated ON rated.id = edition.ratedId
LEFT OUTER JOIN videoFormat ON edition.videoFormatId = videoFormat.id
LEFT OUTER JOIN aspectRatio ON edition.aspectRatioId = aspectRatio.id
LEFT OUTER JOIN studios ON dvdpedia.studioId = studios.id
LEFT OUTER JOIN country ON dvdpedia.countryId = country.id
LEFT OUTER JOIN ( SELECT writer2dvd.titleId
, GROUP_CONCAT(CONCAT(writers.name, ' ', writers.lastName) SEPARATOR ', ') AS writers
FROM writer2dvd
INNER JOIN writers
ON writers.id = writer2dvd.pId
GROUP
BY writer2dvd.titleId ) AS w
ON w.titleId = dvdpedia.id
LEFT OUTER JOIN ( SELECT actor2title.titleId,
GROUP_CONCAT(CONCAT(actors.name, ' ', actors.lastName) SEPARATOR ', ') AS cast
FROM actors
INNER JOIN actor2title
ON actors.id = actor2title.pId GROUP BY actor2title.titleId) AS a
ON a.titleId = dvdpedia.id
LEFT OUTER JOIN (SELECT lang2title.titleId, GROUP_CONCAT(languages.lang SEPARATOR ', ' ) AS languages
FROM languages
INNER JOIN lang2title ON lang2title.pId = languages.id) AS l
ON l.titleId = edition.id;