Video System

[Phudsen]

Hi,

I am creating a system for a friend who has a video shop. Everything was going OK until he asked the following:

1- For the same title he sometimes has DVD, VHS and VCD, he wants to keep separate quantities for each. How can I do that without repeating the Film title three times.

2- He purchases a DVD today for 20$, let's say 10 copies of "Lord of the Rings", then after sometime and even before the quantity is "0", the supplier makes a good offer for the same title and sells it for 10$, he purchases another 10 copies. How to maintain separate prices for the same title with separate quantities, i.e

Lord of the Rings for 20$ still has 4 copies
Lord of the Rings for 10$ still has 10 copies

How can I keep separate prices and above all how can I sell these movies and know which is which.

I need theoritical replies not codes or queries, just the plan how to do that.

Thanks a lot
Paulin

 

[BNPMike]

You seem to be saying there are films and there are physical versions. So that's two entities. Store all the film details in the film table and create separate records for each of DVD, VHS and VCD. You don't want to do it but of you try to treat things as clumps you are going to have difficulty later. Supermarkets already change the barcode on items when anything changes, but they want RFID. There is a reason.

To get over the pricing thing, I think again you are going to have to maintain product numbers eg PLUs separately for each price group so when the shop sells an item you automatically detect its price and which set of stock it is coming from.

Limit the keying by good form design and by keeping product numbers as short as possible.

 

[Phudsen]

Hi BNPMike,

Thank you for your quick reply. I have a table that has the following fields:

CassetteID
Title
PurchasePrice
Actor
Actress
Type (DVD, VCD or VHS)
Category

What is the best planning to create two tables? is the following a good plan?

Table1
------
CassetteID (PK)
Actor
Actress
Category

Table2
------
CassetteID (FK)
Title
PurchasePrice
Type (DVD, VHS, VCD)

The data entry form will have both tables related.

Is it OK or you have a better idea?

Regards
Paulin

 

[Olaf4]

No. That is not solve the problem.
For the first question, I suggest do this.
1. Create a table for Formats. Looks like FormatID, Format (to put DVD, VHS, etc.)
2. Create a table for the film. FilmID, Title, etc. Like your first table above.
3. Create a third table FormatReg. Looks like FilmID,
FormatID. Because it is a many to many relationship. It a table between the two fiert ones.
4. Link the three tables. FilmTable with FilmReg. FormatTable with FilmReg.
Now, you have a film with diferent formats.
This solve your first question.
Later we will work on the others. Quantity of each and Price.

But for the price need more info. Do you like an average price? or need track each price independent? or you use that price to calculate the sales price?

 

[Phudsen]

Hi Olaf4,

Thank you for your detailed reply and suggestions.

The price is needed to calculate Profit. It is needed also to know which version of the movie we are selling.

e.g:
Lord of the Rings , Qty: 10 Price 25 Purchased on 3 March 04
Lord of the Rings, Qty: 6 Price 15 Purchased on 20 March 04
Lord of the Rings, Qty: 3 Price 10 Purchase on 22 March 04

The second and third versions were sold on big discount. Now I want to be able to sell the quantities from each and deduct the quantity, keeping in mind the price either for selling or for calculating lost or profit.

How can I sell 4 copies of the first one for 27$ and deduct the quantity from it. Is it by giving separate ID?

Thanks
Paulin

 

[Olaf4]

I have a question. In the real world do you keep this particular film separated in three groups (by purchase price) or they are mixed?
If they are mixed, how do you know the 4 films you are selling belong to the first group?

 

[Phudsen]

Hi,

They are mixed.

Maybe he they can be recognized by a special ID.

Thanks