Re: Newbie DBase architecture questions

Hi there,

I've been around this type of problem for a while, but not got a solid fix on it yet.

My first thoughts are that you should separate the products from the hierarchy. Where a product (lava lamp etc) sits in the online catalogue isn't really much to do with the product, but rather where us humans want it to appear. Therefore you could look to create a table(s) to represent hierarchy, and then 'pin' products into various points in this hierarchy.

Then comes creating the tables to represent the hierarchy, which is the difficult bit. I think Joe Celko has given some recommendations for this sort of thing in one of his books.

From a relational point of view I suppose you're lookng at a m:m self-referential relationship (that can't be the correct term!?). I've avoided using these in practice, since you have to resort to things like tree-walking and such, which is complex and slow IMHO.

If you can't make headway with this, then you may look at a fixed hierarchy, where you have a maximum of n levels deep. From a human perspective this may not be a bad thing, but to me it doesn't *feel* right.

Sorry I couldn't be of much help here, but it will be interesting to hear what others say!

Tobin Harris

"abaxaba" <msanderso_at_hotmail.com> wrote in message news: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 Fri May 17 2002 - 02:06:28 CEST