Обсуждение: Design advice needed.

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.

Thanks

Mark

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

--- Mark Kelly <pgsql@wastedtimes.net> 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.

I think that trees in sql are do-able:
http://www.intelligententerprise.com/001020/celko.jhtml;jsessionid=ZJQKH5QHJICYKQSNDLRCKHSCJUNN2JVN?_requestid=384002

Check out the nested set model. The two other alternatives to this tree model are adjacency list
model and path enumeration model.  However, I like nests set the best for the features it provides
but these features come at the expense of some complexity.

The other option is to create a table hierarchy using the postgresql table inheritance or by
explicitly creating a hierarchy yourself.
http://www.postgresql.org/docs/8.2/interactive/ddl-inherit.html
http://www.utexas.edu/its/windows/database/datamodeling/dm/hierarchies.html

Hope this helps,

Regards,
Richard Broersma Jr.

On Wednesday 04 April 2007 23:04, Sean Davis wrote:
> Mark Kelly wrote:
> > 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.
>
> The categories are not really hierarchical, are they?  A person could
> just as easily start with "honda" in the example above as with
> "exhaust".

Light-bulb appears above head.

> 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.

That's absolutely ideal. I have actually done another database this way in
the past (mapping books into genres) but the clients insistence on the
hierarchy had kinda locked me into looking at it his way IYSWIM. Thank
you. Not only can I proceed happily with this now, I can offer facilities
above and beyond what the client is asking for which I always try to do
when the spec is informal enough.

Thanks again,

Mark

On Wednesday 04 April 2007 23:08, Richard Broersma Jr wrote:
> I think that trees in sql are do-able:
> http://www.intelligententerprise.com/001020/celko.jhtml;jsessionid=ZJQKH
>5QHJICYKQSNDLRCKHSCJUNN2JVN?_requestid=384002

Thanks taking the time to reply Richard. Sean's post has given me all the
inspiration I need for now, but I have bookmarked your links for later.

Regards

Mark