Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 9C7FD6337C5 for ; Thu, 7 Jan 2010 00:01:16 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 15357-07 for ; Thu, 7 Jan 2010 04:01:06 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-ew0-f226.google.com (mail-ew0-f226.google.com [209.85.219.226]) by mail.postgresql.org (Postfix) with ESMTP id 567106335E5 for ; Thu, 7 Jan 2010 00:01:05 -0400 (AST) Received: by ewy26 with SMTP id 26so16224201ewy.3 for ; Wed, 06 Jan 2010 20:01:04 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:in-reply-to:references :from:date:message-id:subject:to:cc:content-type; bh=6CYy3YlRkkyLWCpoi8Kxx41lTdMPgYfu9xxKoZWO3UM=; b=Kd65NAypg0Kh0ar0XsoCQjkBkm0gan0/QuLZscZnrzUOB9XDSUqI8bH6Xnu7tW/SVo IuAazGCsRJrL/cRCJ16x10EI2tksimUh0UxH8UGIijfTVnIEuU/ufMKF4WaDVq4islog Y3a4sMFyyIfMdDc7zKxygi0ZqBm9CWi9Zgkek= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:from:date:message-id:subject:to :cc:content-type; b=Yto85gh+OXghshZo/BGeSDBwqALFfNP/G0M63B7Z01l5jHctGYoSQ3V1zVGmZVqrtK SIkcVqPwVkofbAJ+ZT3LjA9KF14PTAeqBQu+GHWzjNyKC5f31uL4P6clGwBHrdD4eh7Q dYlRbK3thdS7T/IQpU8M6V4aoBDAXKntz88jI= MIME-Version: 1.0 Received: by 10.216.89.14 with SMTP id b14mr629893wef.76.1262836863433; Wed, 06 Jan 2010 20:01:03 -0800 (PST) In-Reply-To: <4B452739.1020908@cam.ac.uk> References: <22f7878e1001061553j69b3b461hadd4633c58c5304f@mail.gmail.com> <4B452739.1020908@cam.ac.uk> From: Nikolas Everett Date: Wed, 6 Jan 2010 23:00:42 -0500 Message-ID: Subject: Re: noob inheritance question To: Richard Neill Cc: Zintrigue , pgsql-performance@postgresql.org Content-Type: multipart/alternative; boundary=0016e6d977fda77be3047c8b1ea4 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.59 tagged_above=-10 required=5 tests=BAYES_00=-2.599, FH_DATE_PAST_20XX=3.188, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 201001/71 X-Sequence-Number: 36951 --0016e6d977fda77be3047c8b1ea4 Content-Type: text/plain; charset=ISO-8859-1 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 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 > --0016e6d977fda77be3047c8b1ea4 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Inheritance=A0would only make sense if each of your categories had more col= umns. =A0Say if you had a "wines" category and only they had a ye= ar column. =A0Its probably not worth it for one or two columns but if you&#= 39;ve got a big crazy=A0heterogeneous=A0tree of stuff then its probably app= ropriate.

I'm with Richard in that it sounds like the right way to= solve your problem is to have a "categories" table and a "p= roducts" table. =A0Let the categories table have a reference to the pa= rent. =A0I suppose just like they do in the first section of=A0http://= dev.mysql.com/tech-resources/articles/hierarchical-data.html=A0. =A0The= other sections on the page just seem like overkill to me.

On Wed, Jan 6, 2010 at 7:13 PM, Richard Neil= l <rn214@cam.ac.uk<= /a>> wrote:
Zintrigue wrote:

I'm hoping the inheritance feature will be a nice alternative method fo= r me to implement categories in particular database of products I need to k= eep updated. I suppose in MySQL I would probably do this by creating, for e= xample, 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 nes= ted 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 em= pty root "product" table, then create, for example, a "spirt= s" table that inherits from products, and "rums" table that = inherits from spirits, and then "aged rum", "flavored rum&qu= ot;, 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 l= owest 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 t= o 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 t= racking which item is a subset of the other.
=A0You could do it by having 2 columns:
=A0 =A0id, parent_id =A0 =A0 =A0(each integer and indexed)
or you could do it by having 2 columns:
=A0 =A0id, list =A0 =A0 =A0 =A0 =A0 (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 l= ists better suited to your questions.

--0016e6d977fda77be3047c8b1ea4--