Inheritance would only make sense if each of your categories had more columns. Say if you had a "wines" category and only they had a year column. Its probably not worth it for one or two columns but if you've got a big crazy heterogeneous tree of stuff then its probably appropriate.
I'm with Richard in that it sounds like the right way to solve your problem is to have a "categories" table and a "products" table. Let the categories table have a reference to the parent. I suppose just like they do in the first section of http://dev.mysql.com/tech-resources/articles/hierarchical-data.html . The other sections on the page just seem like overkill to me. On Wed, Jan 6, 2010 at 7:13 PM, Richard Neill <rn...@cam.ac.uk> wrote: > 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 >
