DB Design problem

[Amin]

Dear all,

I want to design a P.O.S system for the footwear shop I
have, I really can't make the logic and my brain's stopped

its like this:

- Each Brand has many models
- Each Model has many colors
- Each Color has many sizes

Let's take a look at a sample from my stockhandling report:

Brand: Model: Color: Size: Quanity:
Clarks
Berlin
black
40 2
41 2
42 1
brown
43 1
44 2

Q. Does this mean each that each difference in the 4
variables makes a (different) product??

Q. Do I need another table called product where all the 4
primary keys are forgien keys in it?

Q. Where shall I store the quantities?

Q. Is it a really complicated matter or am I making it so?

Q. Do you know a ready made software?

Thanks!

 

[Larry]

I would create an Inventory table that has a 4-field key
(brand, model, color, and size) and then a field for
quantity.

Depending on how complex and functional you want your
application to be, you can also create lookup tables for
the key field items to ensure that valid entries are
made. It would also benefit you if you needed a user-
friendly front end developed to be able to easily add new
brands, products, etc.

Good luck,
Larry

 

[Tim Ferguson]

Q. Does this mean each that each difference in the 4
variables makes a (different) product??

Presumably: is a blue Clarks Berlin different from a green Clarks Berlin;
and is there even such a thing as a H&M Berlin? And is the green in Clarks
the same as the green from Scholl?

Q. Do I need another table called product where all the 4
primary keys are forgien keys in it?

Don't think tables, think entities. You have Manufacturers, Models, Colours
(?), StockItems.

Q. Where shall I store the quantities?

Your diagram suggests that it's an attribute of the StockItem, but only you
can answer the question for real.

Q. Is it a really complicated matter or am I making it so?

No and not really. But if it is your first project, do you really want to
bet your shop on it? Get your stock wrong and you could (a) go broke or (b)
get in serious trouble with taxes etc.

Q. Do you know a ready made software?

Not from personal experience, but there must be loads. Have you looked
through WinFiles.com; trade journals; other traders; etc?

Hope that helps


Tim F

 

[Amin Abu-Dahab]

Tim .. thank you very much!