I have an ecommerce project I'm working on for which I have to develop a database that will house many products under many categories with unlimited subcategories/levels. For example:

baseball
baseball \ weightlifting
baseball \ weightlifting \ upper body
baseball \ weightlifting \ lower body \ ...

The levels of subcategories must be completely scalable, as the client wants to be able to add and subtract at will. Further, there is the idea that some subcategories will transfer across other categories (ex: weightlifting would be a subcategory of multiple sports).

In previous database designs I've simply had a category table and maybe a subcategory table; obviously that won't work here, as the number of levels of categories is unknown.

Does anybody have any suggestions or examples to point me in the right direction?

Any help would be greatly appreciated.

Brian

The category problem is solved by a recursive relationship:

Category(categoryID, parentCategoryID, attributes)

However, when you throw in transfers across categories you are looking at a network instead of a hierarchy.

You would have to actually manage Nodes and Associations.

First you have a Node.
Node(NodeID, attributes)

Then you have an association
Association(AssociationID, StartNodeID, EndNodeID)

The management of the network would be programmatic. In otherwords you would have to conceive your own rules for managing the structure.

Thanks certus, I appreciate it.

I have yet to implement a "network" design. Are there any references you can point me too such as books, websites, etc.?

Thanks again,
Brian

A network structure you may be familiar with is the system tables of your RDBMS. Think in particular how relationships and tables are managed. You would be interested in how the entity/relationship logical structure exists not the management of instances per se.

Note that this is different than a Node/Association structure as I described it. I'm looking for a good example for you.

Alright, google has not yielded what I am looking for so I'll tell you a bit about the network.

First, nodes are independent. They can exist whether relationships to them exist or not. This seems unconventional until you recognize that a table can exist without relationships.

Second, Associations are directional. An association has a Source (or start) node and a Target (or end) node. This directionality keeps your network manageable. An association cannot exist without both a Source Node and a Target Node.

Third, nodes can be created, updated and deleted. Associations can only be created and deleted. When a Node is deleted all the Associations which have the Node as a Source and Target are deleted. This can cause islands of Nodes to exist, but you will not lose track of the Nodes. They are all within one table. Simply create an Association between the island Node and the Main body of nodes and harmony is restored.

If that makes sense to you we'll discuss it further.

That's about it.

I'm going to recommend not pursuing a network solution.

It does not fall within the bounds of normal technique and could consequently make your model very difficult to maintain.

Without a lot more access to the requirements definition (probably talking to your client), it isn't clear (at least to me) exactly what they need/want. Without understanding that, I don't have a prayer of giving you good advice.

Based on the previous messages written by Certus, I suspect that the node/association model is what will be required to implement what you've described. As Certus pointed out, that may not be easy, but it gives you the flexibility that you seem to need (based on your description of the requirements).

My first suggestion is that you read up a bit on the associative model of data (check a good whitepaper on SmallTalk or MUMPS, or a data modeling book for more details). Once you understand this concept, go back and talk more with your users to see if that is what they really need, or if a simpler solution will do what they need.

-PatP