Re: Design advice needed.

Mark Kelly wrote:
> Hi
>
> I have been asked to produce a website for selling a very varied range of
> items, around 5000 in all. The client wants to be able to categorise items
> in a hierarchical tree, along the lines of "exhaust > round > gold >
> honda" (it's motorbike parts) but wants to be able to use an unspecified
> number of levels and have that number differ between categories. For
> instance, exhausts might drill down 4 layers of categories, whereas stands
> may only have 2 layers, and he can't tell me the max number of layers as
> his range not only varies alot but changes frequently. I hope I am
> explaining this okay.
>
> Obviously I cannot simply add a column for each successive layer of
> categorisation if I don't know in advance how many layers I need. Can this
> kind of "free-form" categorisation be done with Postgres? I am prepared
> for the fact that I may have to limit him to an arbitrary depth, but I'm
> rather curious about the if and how of this :)
>
> The site is written in php, and if I have to do some of the heavy lifting
> in the code there, thats fine. I'd still need advice about setting up the
> tables though.
>
The categories are not really hierarchical, are they?  A person could
just as easily start with "honda" in the example above as with
"exhaust".  I would think instead of making a categories that contains
all the categories.  You will need an items table that contains all the
items.  Finally, you will want a table that maps items to categories.
Your search strategy above can then be as simple as searching for items
that belong to category "exhaust" first.  At the next step, you can do a
search for items that have both the categories "exhaust" and "round",
and so on.  At each step, you can also pull out all the unique
categories in the items already chosen to allow the drill-down to
proceed with simple point-and-click.  As a further refinement, you could
assign many of the categories to one of several groups such as
manufacturer, color, shape, etc. and offer the user a choice to filter
by manufacturer, color, etc.  Hopefully, you get the idea.

Sean