2010-05-19, 12:20
jmarshall Wrote:So perhaps we could constrain things somewhat into broader categories such as:
1. Media (movies, episodes, music videos, songs, pictures)
2. Collections (sets, seasons, shows, albums, picture albums, album sets)
3. People (artists, actors, directors, writers, Mum, Dad etc.)
4. Characters (a Media/Collections<->People link table)
5. Groups (bands, families) ?
All of the above have attributes, and the attributes may be specific enough that we could use a flat table or a key/value attributes table or a combination thereof.
firnsy Wrote:The schema is initially focusing on video. If we get this right then ideally we can add their requirements with minimal effort and negligible cost of query times.
Regarding expanding the schema for all media types, I can see two options:
- Put all content in content table and add a "mediaType" field, which can be specific enough like "movie, episode, movie set, series, album, track, etc". Games could be added easily here too.
Common fields between media types can be identified and have different meaning according to the "mediaType".
For example "episode" and "season" fields can be changed to something like "partNumber" and "setNumber". For episodes these will be the episode & season numbers and for audio tracks the track and disc number (which I miss badly).
- Create separate videocontent, audiocontent, picturecontent, gamecontent tables.
This breaks the uniqueness of idContent, but it could be corrected by having a global sequence for obtaining a unique idContent to use in the various content tables.
Perhaps this is more straightforward solution. Specific media attributes can be stored directly into these tables and minimizing the recursive lookups.