Somewhat complicated database design for movie collection/library, need guidance

Question

I'm a bit rusty on my databases, but maybe you can help out. I want to create a table containing movies bought on, for example, DVD and Blu-ray. As you know there are DVD boxes which contain several movies and each movie in a box can be a special edition, such as a director's cut, etc. You can have both single movies and boxes in your collection/library.

How to structure this? I don't remember the proper notation, but here goes:

movies(_id, title, year)
boxes(_id, title, format)
movies_in_box(_*movie_id, _*box_id, edition)
collections(_*user_id, _*id [references either movies or boxes], _type [enum for movie or box], edition, format)

_ = primary key, * = foreign key

Does this make any kind of sense?

Answer 1

Your hierarchy starts with a collection, which represents all the items belonging to a user. An item can either be a standalone film or a box set, which in itself comprises several films. I think that the 'format' field should belong to the movie and not to the box (you might have one dvd and one blu-ray in the same box).

There seems to be slight redundancy regarding the 'edition' field but I don't think that's important. If you can work around the fact that you have a foreign key - _*id - in the collections table which references either a film or a box, then the structure is ok, but I think it would be better to represent every film as a box set, even if the box contains only one film. Then your structure would be

USERS
user_id (PK)
username

MOVIES
movie_id (PK)
movie_name
year
format

BOXES
box_id (PK)
box_name
edition (I assume that this includes the year) 

BOXCONTENTS
box_id (PK, FK)
movie_id (PK, FK)

COLLECTIONS
user_id (PK, FK)
box_id (PK, FK)