The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

need help with database design

Hi,

I've been banging my head against the desk over this for a few days now, I can't find an elegant way to accomplish it so I was wondering if anyone here could help?

Basically I'm building an e-commerce site.  The products are in categories, which may be 2 or 3 deep.  The problem is that within a product type there is no fixed number of levels:

Main_Product
...product_category
......product_range
.........product
.........product
.........product
...product_category
......product_range
.........product_style
............product
............product
............product
...product_category
......product
......product
......product

which is making the generation of the product navigation a nightmare for me, I just can't seem to work out a way to get it to list the types first, then work out whether or not to show another level or go straight to the products.

I have a category table:

cat_id (pk)
cat_name
cat_parent

I also have a product table:

prod_id
prod_name

so what I've been trying to do is first show all the types, then have a link that passes the cat_id for that type back as a querystring.  It then looks for a category with that cat_parent value, and so on. 

This works fine when you know you have, say 2 levels, but if its a mix how can I efficiently know when to stop and display the list of products, without having to explicitly plan for every eventuality and end up with a maze of IF statements?  I'm assuming its going to involve recursion, but that's where I'm getting stuck...

Hope someone can help, I'm having to work over the holidays to get this project done and this is really holding me up.  I'm using ASP.NET 2.0 and C#.  I haven't got all the data in the database yet so a change of design wouldn't be a great problem at this stage.

Thanks in advance
surreal
Monday, December 25, 2006
 
 
sorry, that product table should have a reference to the category table, obviously...
surreal
Monday, December 25, 2006
 
 
what i think you need to do(imho) is just have 2 tables

CatalogEntry
id (pk)
parentid (fk)
name
type (fk to CatelogEntryType)

CatelogEntryType
id
name(values ...product_category,product_range,product_style,product)

this should allow you to store arbitrary levels
siddharth
Monday, December 25, 2006
 
 
Looks like you are doing trees and SQL is about sets, so you have a lot of pain ahead.

Look up adjacency lists and the nested set model. One reference is http://www.developersdex.com/gurus/articles/112.asp?Page=2
son of parnas
Monday, December 25, 2006
 
 
I would suggest keeping your base tables simple and put the complexity into a view of the data. Create your complex view from the information in table "ProductRelationships".

If you display a product that has both a Range and a Style, then add links (shown before the products) to retrieve only data with specific Range and Style ID's.

Check out http://www.summitracing.com/ for a good example of how to work with this type of problem. Do a search for carburetor and you'll see they have "Part Type", "Product Line", etc. listed as links.

*Base Tables*
Table: Products
ProductID
Product

Table: Categories
CategoryID
Category
Parent_CategoryID

Table: Ranges
RangeID
Range

Table: Styles
StyleID
Style

*Relationship Table*
Table: ProductRelationships
ProductID (key)
CategoryID (nullable)
RangeID (nullable)
StyleID (nullable)
MikeS Send private email
Monday, December 25, 2006
 
 
thanks guys, there's some good stuff to think about there... Mike, I especially like the idea of separating out the tables, that might work.

I've been trying to pseudocode a solution up just using one ID for whichever item you've clicked on, then working that backwards to find the id's path up to that item and using that to build the nav but its tough going.

thanks again
surreal
Monday, December 25, 2006
 
 
UPDATE - I got this to work finally, using recursion.  Basically I did what I alluded to in my previous post:

Still have a product table and a self-referencing category table as before

CAT
cat_id
cat_name
cat_parent

PROD
prod_id
cat_id
prod_name

When the user goes to the page they pass a categoryid in the querystring:  mydomain.com?cat=23

I grab the whole of the category table into a disconnected dataset so I only have to hit the database once.  I then use whatever catid they have passed and work backwards thru the dataset and put the cat_parent numbers into an arraylist, so the array might contain 23,9,2,0

Then I use the arraylist and the dataset in a recursive method that uses the numbers in the arraylist to branch at the correct points.  When I reach the end of the array I look at the product table to see if there are any products for that branch.  This means I can have products at any branch or indeed at multiple points along the tree I guess...

Not the cleanest of solutions, ended up being more of a change-that-and-see-what-happens kind of thing but it works now so I can look at tidying it up.

Thanks again for the suggestions, much appreciated!!

dave
surreal
Tuesday, December 26, 2006
 
 
It might be worth checking out something like ZenCart, a nice piece of open source ecommerce software.  I can't recall exactly how they dealt with this problem, but however they did it it's a solution that works well.  Could be reassuring to look and see that you're using a similar method, or maybe ZenCart will point the way towards a better method.

http://www.zencart.com/
Herbert Sitz Send private email
Tuesday, December 26, 2006
 
 
+1 Son of Parnas - Look up adjacency lists and the nested set model.

That's the best way to do it.  Very good performance, relatively easy to implement.
Mike Stephenson Send private email
Wednesday, December 27, 2006
 
 
The book "Joe Celcko's Trees And Hierarchies In SQL For Smarties" should prove quite useful for this stuff.
AH
Wednesday, December 27, 2006
 
 
The Art of SQL has a very interesting comparison between different tree-in-SQL-implementations.
Kaboing Send private email
Friday, December 29, 2006
 
 
I'm not completely sure I understand the issue here, but one approach that might be helpful is a root_id column on the category table.  For top level categories this would be null, but for all child cats this would be populated with the top-level parent id.  Also a depth column that gets populated with the number of parents the record has.

So given a cat id, to get all the cats above, you could just query on root_id where depth < the current record's depth, then join in the products having those cat ids.

Good luck, sucks to get stuck with this over the holiday week.
dave
Friday, December 29, 2006
 
 
Make 2 tables, categories and products.

Categories:
id
name
categoryId

Products:
id
name
categoryId

if categoryId is 0 in the table categories then it is a top category, if it's not it is a sub-category... By doing this you can have unlimited categories. I've developed many ecommerce systems with categories and subcategories and I think this is the best way to go.
Kjartan Olason Send private email
Monday, January 01, 2007
 
 

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
 
Powered by FogBugz