Message-ID: <4B452739.1020908@cam.ac.uk>
Date: Thu, 07 Jan 2010 00:13:45 +0000
From: Richard Neill <rn214@cam.ac.uk>
Organization: RUO3.org
User-Agent: Thunderbird 2.0.0.22 (X11/20090608)
MIME-Version: 1.0
To: Zintrigue <zintrigue@gmail.com>
CC: pgsql-performance@postgresql.org
Subject: Re: noob inheritance question
References: <22f7878e1001061553j69b3b461hadd4633c58c5304f@mail.gmail.com>
In-Reply-To: <22f7878e1001061553j69b3b461hadd4633c58c5304f@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

Zintrigue wrote:

> I'm hoping the inheritance feature will be a nice alternative method for 
> me to implement categories in particular database of products I need to 
> keep updated. I suppose in MySQL I would probably do this by creating, 
> for example, one table for the products, and then a table(s) for 
> categories, and then I'd be faced with a choice between using an 
> adjacency list or nested set paradigm for, say, breadcrumb links in my 
> private web app.
> 
> On the other hand, in Postgres what I'd like to do it just create an 
> empty root "product" table, then create, for example, a "spirts" table 
> that inherits from products, and "rums" table that inherits from 
> spirits, and then "aged rum", "flavored rum", et al, which inherit from 
> rums.
> 
> In this scenario, my idea was to have all my fields in "products" and to 
> not add any additional fields in the child tables. Also, only the lowest 
> level of child tables in any given branch of products would actually 
> contain data / rows.
> 
> Assuming this is a good design,

May I venture to stop you there. This sounds like you are doing it
The Hard Way.

In particular, each time you add a new category, you're going to have to 
add a new database table, and your schema is going to get to be 
horrible. Inserts aren't going to be much fun either.

Rather than adding multiple child tables, may I suggest some other way 
of tracking which item is a subset of the other.
  You could do it by having 2 columns:
     id, parent_id      (each integer and indexed)
or you could do it by having 2 columns:
     id, list           (id is integer, list is eg "1,3,5,13")
(where the list is a comma-separated list, or an array, and holds the 
full path)


Depending on scale, you may be able to choose a simple algorithm instead 
of hunting for the most efficient one.


Best wishes,

Richard


P.S. This is the performance mailing list - you may find one of the 
other lists better suited to your questions.