Re: Newbie DBase architecture questions
Date: Fri, 17 May 2002 07:59:08 GMT
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:
>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?
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;Received on Fri May 17 2002 - 09:59:08 CEST