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.

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance