Re: noob inheritance question - Mailing list pgsql-performance
| From | Nikolas Everett |
|---|---|
| Subject | Re: noob inheritance question |
| Date | |
| Msg-id | d4e11e981001062000r18949e7cg95cc110d0f7a1dd@mail.gmail.com Whole thread Raw |
| In response to | Re: noob inheritance question (Richard Neill <rn214@cam.ac.uk>) |
| List | pgsql-performance |
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:May I venture to stop you there. This sounds like you are doing itI'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,
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
pgsql-performance by date: