From: Richard Neill <rn214@cam.ac.uk>
To: Zintrigue <zintrigue@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: noob inheritance question
Date: Thu, 07 Jan 2010 00:13:45 +0000
Message-ID: <4B452739.1020908@cam.ac.uk> (raw)
In-Reply-To: <22f7878e1001061553j69b3b461hadd4633c58c5304f@mail.gmail.com>
References: <22f7878e1001061553j69b3b461hadd4633c58c5304f@mail.gmail.com>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: rn214@cam.ac.uk, zintrigue@gmail.com
Subject: Re: noob inheritance question
In-Reply-To: <4B452739.1020908@cam.ac.uk>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox