From: Nikolas Everett <nik9000@gmail.com>
To: Richard Neill <rn214@cam.ac.uk>
Cc: Zintrigue <zintrigue@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: noob inheritance question
Date: Wed, 6 Jan 2010 23:00:42 -0500
Message-ID: <d4e11e981001062000r18949e7cg95cc110d0f7a1dd@mail.gmail.com> (raw)
In-Reply-To: <4B452739.1020908@cam.ac.uk>
References: <22f7878e1001061553j69b3b461hadd4633c58c5304f@mail.gmail.com>
<4B452739.1020908@cam.ac.uk>
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@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
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: nik9000@gmail.com, rn214@cam.ac.uk, zintrigue@gmail.com
Subject: Re: noob inheritance question
In-Reply-To: <d4e11e981001062000r18949e7cg95cc110d0f7a1dd@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox