Обсуждение: 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