Hello,
I am complete noob to Postgres and to this list, and I hope this will be the appropriate list for this question.
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, what I'm wondering is how inheritance is actually implemented deep down inside Postgres, if it's anything at all like JOINS (say, in the case of merely doing:
SELECT * FROM "flavored_rum" (the lowest level in a given branch)
or
SELECT * FROM "spirits" (the root level, or some intermediate level in a given branch)
I'm wondering if there's any performance penalty here, analogous to the penalty of JOINs in a regular RDBMS (versus an ORDBMS).
If anyone can offer in any insight as too how inheritance is actually executed (compared to JOINs especially), I'd be most grateful.
Thank you,
DG