Newbie DBase architecture questions
Date: 16 May 2002 13:19:44 -0700
Message-ID: <b89e0a91.0205161219.780e52dc_at_posting.google.com>
[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?
On another note -- each of these products can have multiple options, which could/not alter the price. For instance, a lamp could come with a three-way option, a lavalamp could come in multiple colors, or a lightbulb could come with different wattages.
Of course, one option could be applied to multiple products.
To tackle this -- I've thought of creating a foreign-key table, with just productID's and optionID's. I'm not quite sure if this is the right approach, as this foreign key table wouldn't seem to have a primary key. There would seem to be duplicate productID's [one for each relevant option per product], and duplicate optionID's [for those options that are appropriate to more than one product.] I'm planning on indexing the productID field, as my sql would be concerned with getting the options for a single product.
Is this the best tact to take regarding this problem? Or does the duplicate field values accross rows mean that my data isn't properly normalized?
Many thanks in advance to one and all for any assistance to this, ahem, query! Received on Thu May 16 2002 - 22:19:44 CEST