Re: [tlug] Database design in Japan. (question)

[Mark Makdad <mark@example.com>]

Birkir A. Barkarson wrote:
> In front of me I have a database design where a product list table has a
> product type property (this property is a hierarchical tree) and
> is using six different fields to store each of the levels of the type 
> tree the
> product belongs to.
>
> My instinct would be to have an external type per product table (with a
> field pointing to the product table and another to a type table). 
> Hierarchical
> info could be in the type table (a parent field) if a single hierarchy 
> is used
> or in a separate one for multiple hierarchies.
>
> Can anyone chip in some good reasons for and against the 
> implementation I am
> faced with on the one hand and my suggestion on the other?
Foremost, I need a little clarification on the circumstances.  The 
current design sports one table "product" where there are six different 
fields used to store "each of the levels of the type tree the product 
belongs to".  That's where I'm getting lost.

Does this mean that for any given product, there is only one of the six 
fields containing data, and that that in-and-of-itself is providing 
value/meaning to the product list?  Sounds horrendous.  That can't be 
what you mean...?

I have implemented a similar (if I am thinking of the right design here) 
system -- products and categories -- and I wanted to have infinitely 
nesting categories, and the possibility to have items in any category.  
I chose one table for products with a single external key to a category 
table (or type, what have you).  The category table also had a field 
referring to its parent (or NULL if a top-level category).

Maybe I'm answering a different question, but that's how I'd approach 
that.  It makes moving/adding items very easy.

Cheers

Mark Makdad