Re: Design advice needed.

--- Mark Kelly <pgsql(at)wastedtimes(dot)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.