Re: Questions about my strategy

On 31 Jul 2002, Rob Brown-Bayliss wrote:

> On Wed, 2002-07-31 at 02:34, Andrew Sullivan wrote:
> > Why not three codes: model, colour, and size?  Then you can query all
> > by model, and limit by size, colour, or both.
>
> How do you mean three codes?
>
> If you mean having more than one product code for a type of shoe thats
> what I am trying to avoid.  I worked for acompany that did that a long
> time ago, the product was steel, and having 15 or more different lengths
> of a steel chanl meant having 15 product codes.  It was often easier to
> walk out to the store and look for the product than to query each
> product code at the counter...

What Andrew is saying, I believe, is that you need to determine what defines a
shoe. What defines a shoe is it's model, it's size and it's colour. Therefore
you absolutely need a unique code to describe each variant. Either that or you
say the combination of (model, size, colour) is the unique code. Personally one
of the two designs I immediately thought of (the second one actually but the
first has more relations) that I would therefore consider strongly goes
something like:

shoe_model:
    mid primary key,
    name,
    description,
    manufacturer,
    ...

shoe_stock:
    mid references shoe_model(mid),
    colour,
    size,
    count


That structure gives you everything you want whether count is a difference or
an absolute, at least I think it does. I believe it's also more of a
correct solution than the convoluted way you seem to be thinking. Some database
expert will now correct me on those points. :)


--
Nigel J. Andrews
Director

---
Logictree Systems Limited
Computer Consultants