Drawing an entity-relationship diagram for a movie sales business domain

Question

I want to keep track of movies that exist in different formats (for example, DVD, Bluray, etc.). The price for each format differs from the others as well as the quantity of each format, so I came up with the entity-relationship diagram (ERD) shown below:

Is this correct from a design perspective? Does this implies redundancy? I don't understand how will this be retained in a table when implemented in a relational database.

Would it be better to do it like in the diagram that follows:

Additional information

Here's more descriptive information about what I want to store at this point of the design:

• I want to store information about sales.

• For each movie that exists in the company I need to keep format, price and stock quantity.

• I will also need to maintain customer information with a unique id, name, surname, address, movies that he/she has already bought and his/her credit card number.

• Finally, I will have a basket that temporary keeps items (let's suppose that other items exist apart from movies) that the customer wants to buy.

Answer 1

I would use (title,prod_date) as the primary key, since movies are not uniquely identified by their title alone (remakes, for example). In my opinion, the first diagram is preferable, the sub-attribute approach in the second diagram seems a bit convoluted to me.

Answer 2

I'd put in stock something like movie_product (assuming you are not going to sell anything but movies) which can look like

movie_product (movie_product_id ,
movie_id, format_id, manufacturer_id, date_produced,
--maybe some other attributes
)

You need to decide yourself what is important to store in the context of your domain (say, you don't care about manufacturer, so there is no need to store id, or, in contrast, it's important to have a flag specifying whether it's a special edition ). Then stock may look like

stock (stock_id, movie_product, current_quantity)

I guess price is changing, to deal with it I'd track it as a separate entity :

stock_price(stock_id, date_from,date_thru, price)
--no updates here, only inserts