Re: noob inheritance question

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.