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

[Curt Sampson <cjs@example.com>]

I assume you're talking about products along the lines of

    electronics / computer / cpu / intel / ...
    electronics / computer / printer / ...
    electronics / digital-camera / dslr / ...
    home-appliances / kitchen / ...

The method you described doesn't seem all *that* bad compared to many of
the DBMS atrocities I've seen, especially given that trees and graphs
in general are, well, let us be generous, "not well supported" in
current relational models. Still, if each product has one and only one
category, just having a key into a table containing each of the entries
above would be more normalized; for multiple categories per product, a
product_id / category_id table would be the equivalant.

More normalized yet would be to have a table of top-level categories,
a second table of second-level categories and their corresponding
top-level categories, and so on. It works if you've got a fixed number
of levels.

Probably the biggest driver of your relational organization of trees
is whether they are fixed in depth or variable. Variable depth methods
can get pretty complex pretty quickly. I often prefer just to make a
text field that contains a list of fixed-length keys in order of level;
this tends to sort and index well, and selection of subtrees is pretty
fast using the LIKE '...%' operator. Or use arrays, if you have them;
it's probably one of the very few justified uses of the array type in
PostgreSQL.

cjs
--
Curt Sampson       <cjs@example.com>        +81 90 7737 2974

On Wed, 4 Apr 2007, Birkir A. Barkarson wrote:

> Since I started working in software development here in Japan I
> accept many of the odd idiosyncrasies I have encountered such as the
> propensity for natural keys to peculiar naming conventions, but
> this latest case just strikes me as being stupid so I feel a need to elicit 
> different opinions.
>
> 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?
>
> Regards,
>
> BAB