I want to build a media list which can hold different media types like movies, tv series, books, games and each of those types can have different attributes attached to them.
Right now I have the following tables:
categories
catid | name
1 | Books
2 | Movies
3 | Tv Series
media
mid | title | catid
1 | Book 1 | 1
2 | Movie 1 | 2
3 | TV Series 1 | 3
4 | Book 2 | 1
5 | Movie 2 | 2
attributes
attrid | catid | name
1 | 1 | number of pages
2 | 1 | description
3 | 1 | year
4 | 2 | length
5 | 2 | cast
6 | 2 | description
7 | 3 | cast
8 | 3 | description
9 | 3 | year
10 | 3 | number of episodes
attribute_info
attraid | mid | attrid | value
1 | 1 | 1 | 213
2 | 1 | 2 | Description...
3 | 1 | 3 | 1996
4 | 2 | 4 | 210 minutes
5 | 2 | 5 | Actor1, Actor2
6 | 2 | 6 | Description...
7 | 3 | 7 | Actor3, Actor4
8 | 3 | 8 | Description...
9 | 3 | 9 | 2010
10 | 3 | 10 | 12
categories holds information about the different media types. media holds information about the individual media entry, attributes defines the different attibutes per category and attribute_info finally holds the info itself.
Now I'm looking for an efficient way to fetch the information via SQL
Ideally I could group all information a media property has into one output row and get something like
Array([mid] => 1, [title] => Book 1, [catid] => 1, [name] => Books, [number of pages] => 213, [description] => Description..., [year] => 1996)
Ways to do this should be inner joins but I don't like to change my PHP code when I add an attribute to a category. What's an elegant in SQL way to achieve my goal?
