Re: Newbie DBase architecture questions

On 16 May 2002 13:19:44 -0700, msanderso_at_hotmail.com (abaxaba) is accused of writing:

>[Note: This was originally posted to comp.databases.rdm, but that
>group doesn't seem to see much activity]
>
>I hope this is the right newsgroup!
>
>I'm a fairly experienced software developer, and have built quite a
>few applications with an rdm [mysql, sybase, informix] backend. But
>accessing a database and executing sql is one thing; this is my first
>attempt at creating a fairly large data-architecture. I'm designing
>an e-commerce site for a client, and their requirements pose a problem
>that I can't quite get my head around.
>
>There store is divided into "departments", and departments are divided
>further into "sub-departments". Of course, each of these
>"sub-departments" can be further subdivided:
>
>[eg Housewares::lighting::incandescent,
>Housewares::lighting::fluorescent, etc]
>
>to some arbitrary limit, perhaps to a sub-sub category.
>
>The problem I'm having is that each of these sub-[sub-]categories can
>belong to more than one main category, such as:
>
>[Housewares::lighting, Hardware::lighting, Entertainment::lighting],
>
>My first thougth is to build a big NxN matrix, with all categories,
>sub categories, and sub-sub categories, and just throw in a 1/0
>[boolean -- is a member], but this doesn't quite seem right. Another
>approach I thought of is to create a new table for each category that
>has "children" categories, but then I could end up with tons of little
>tables, so this doesn't quite seem right.
>
>To make matters worse, at least in my mind, is that each product can
>belong to multiple (sub-sub) Categories. For instance, a lavalamp
>could belong to three categories, Housewares::lighting,
>Entertainment::lighting, and 60sParaphanelia.
>
>Can someone point me to pertinent literature, or offer some ideas to
>get me on the right track?
>

An Update:

As far as the sub-category belonging to more than one category [and, analogously, one product belonging to multiple sub-categories], I think I've came up to a solution:

mysql> 	CREATE table category (catID INT NOT NULL PRIMARY,
	catName (CHAR (60) NOT NULL);

mysql> 	CREATE table subCat 	(catID INT NOT NULL PRIMARY,
				catName (CHAR (60) NOT NULL);

mysql>	create table catChild 	(catID INT NOT NULL,
				subCat INT NOT NULL,
				primary key (catID, subCatID);

Then something like this:

mysql> 	SELECT c.catName, s.catName AS subCat 
	FROM cat c, subCat s,  catChild ch
    	WHERE c.catID = ch.catID 
	AND s.catID = ch.subCatID ORDER BY c.catID;

     	gives me all catNames with the appropriate sub-catNames;