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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
