Re: noob inheritance question

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 <rn214(at)cam(dot)ac(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>