Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I was wondering since i didnt find out any clarification for this. I want to store movies that exist in different formats (dvd, bluray etc) and the price for each format differs from each other as well as the quantity of each format, so i came up with this:

example

Is this correct from a design perspective? Does this implies redundancy? I dont understand how will this be stored in a table. Would it be better to do it like this :

enter image description here

Thanks in advance.

EDIT : I add some more descriptive information about what i want to store in this point of the design. I want to store information about sales. Each movie that exist in the company i need to store format, price and stock quantity. I will also need to store customer information with a unique id, name, surname, address, movies that he/she has already bought and his credit card number. Finally i will have a basket that temporary keeps items (lets suppose that other items exist apart from movies) that the customer wants to buy.

share|improve this question

2 Answers 2

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.

share|improve this answer
    
i updated the question with more info. –  Geo Papas Jan 18 '13 at 17:26
    
a1ex gives good suggestions. If this is going to be used in some real-life commercial system (as opposed to some school assignment) I would strongly recommend not storing credit card numbers - bad idea security-wise and illegal in some countries. –  Noam N. Kremen Jan 18 '13 at 17:36
    
well its not gonna be for commercial systems but just for learning. I am just trying to optimise this as much as possible without opening too much the details of this db. –  Geo Papas Jan 18 '13 at 17:40

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
share|improve this answer
    
I updated the question with more info. btw u confuse me with the relational representation :p Lets keep it at an ER level. –  Geo Papas Jan 18 '13 at 17:27

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.