Mailing List Archive
tlug.jp Mailing List tlug archive tlug Mailing List Archive
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
Re: [tlug] Database design in Japan. (question)
- Date: Thu, 5 Apr 2007 00:35:42 +0900 (JST)
- From: Curt Sampson <firstname.lastname@example.org>
- Subject: Re: [tlug] Database design in Japan. (question)
- References: <46133EF7.email@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 <firstname.lastname@example.org> +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?
- Re: [tlug] Database design in Japan. (question)
- From: Nguyen Vu Hung
- [tlug] Database design in Japan. (question)
- From: Birkir A. Barkarson
Home | Main Index | Thread Index
- Prev by Date: Re: [tlug] (no subject)
- Next by Date: Re: [tlug] Introduction: OpenSolaris
- Previous by thread: Re: [tlug] Database design in Japan. (question)
- Next by thread: Re: [tlug] Database design in Japan. (question)
Home Page Mailing List Linux and Japan TLUG Members Links