Skip site navigation (1) Skip section navigation (2)

Re: Design advice needed.

From: Sean Davis <sdavis2(at)mail(dot)nih(dot)gov>
To: Mark Kelly <pgsql(at)wastedtimes(dot)net>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Design advice needed.
Date: 2007-04-04 22:04:48
Message-ID: 46142100.8050706@mail.nih.gov (view raw or flat)
Thread:
Lists: pgsql-novice
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

In response to

Responses

pgsql-novice by date

Next:From: Richard Broersma JrDate: 2007-04-04 22:08:30
Subject: Re: Design advice needed.
Previous:From: Mark KellyDate: 2007-04-04 21:31:23
Subject: Design advice needed.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group