Re: A form of inheritance with PostgreSQL
От | Greg Toombs |
---|---|
Тема | Re: A form of inheritance with PostgreSQL |
Дата | |
Msg-id | 200703130008.l2D08IGK021628@mi1.bluebottle.com обсуждение исходный текст |
Ответ на | Re: A form of inheritance with PostgreSQL (Steve Midgley <public@misuse.org>) |
Ответы |
Re: A form of inheritance with PostgreSQL
|
Список | pgsql-sql |
Hello, and thank you to Steven and everyone else that submitted input on this issue.<br /><br /> After reading a few moremethods of doing things, I went with the simplest one, as 1. time is of the essence, and 2. I'm stuck with PostgreSQL7.1 on the server I have to develop for.<br /><br /> I set the primary key of the parent class to a serial. Childrenhave an integer column with constraints as the primary key and foreign key to the parent primary key column.<br /><br/> Thanks again,<br /><br /> - Greg<br /><br /> Steve Midgley wrote: <blockquote cite="mid20070309163840.66FFB9FBC7D@postgresql.org"type="cite">Hi Greg, <br /><br /> While not in a C++ framework, you mightfind that it's not too hard to implement something similar in your system - It's called "Single Table Inheritance."References to the Ruby on Rails implementation here: <br /><br /><a class="moz-txt-link-freetext" href="http://wiki.rubyonrails.org/rails/pages/SingleTableInheritance">http://wiki.rubyonrails.org/rails/pages/SingleTableInheritance</a><br /><br/> It's based on Martin Fowler's Patterns of Enterprise Architecture book - please find references to his original patternshere: <br /><br /><a class="moz-txt-link-freetext" href="http://www.martinfowler.com/eaaCatalog/singleTableInheritance.html">http://www.martinfowler.com/eaaCatalog/singleTableInheritance.html</a><br /><br/> The key, I believe, is simply adding a "type" and a "parent_id" to the "class" table, so you can model all your typesand their hierarchical relations. Fowler's diagram is pretty clear. I think then you would store the data in anothertable (or tables) and link into this inheritance structure to establish ancestry for any piece of data (some peopletry to store the data in this table too, but I think that's a mistake personally). <br /><br /> If I understand whatyou're trying to do, you can use this design pattern in your application language to implement an inheritance schemewithout any special database features (i.e. in a SQL-standard manner). <br /><br /> I hope this is helpful, <br /><br/> Steve <br /><br /><br /><br /> At 12:28 AM 3/9/2007, <a class="moz-txt-link-abbreviated" href="mailto:pgsql-sql-owner@postgresql.org">pgsql-sql-owner@postgresql.org</a>wrote: <br /><blockquote type="cite">Date:Thu, 08 Mar 2007 13:01:51 -0500 <br /> From: Greg Toombs <a class="moz-txt-link-rfc2396E" href="mailto:greg.toombs@bluebottle.com"><greg.toombs@bluebottle.com></a><br/> To: <a class="moz-txt-link-abbreviated"href="mailto:pgsql-sql@postgresql.org">pgsql-sql@postgresql.org</a><br /> Subject: A formof inheritance with PostgreSQL <br /> Message-ID: <a class="moz-txt-link-rfc2396E" href="mailto:45F04F8F.8030800@bluebottle.com"><45F04F8F.8030800@bluebottle.com></a><br/><br /> Hello. <br /><br />I'm trying to figure out how to nicely implement a C++ class-like system <br /> with PostgreSQL. Consider the following:<br /><br /> Tables Fruit, Apple, Orange <br /><br /> I want to design the foreign key scheme such that there arerelations <br /> between fruit and apple, and fruit and orange, that imply that apple is a <br /> fruit, and orange isa fruit. <br /><br /> I don't want to eliminate the existence of Apple and Orange tables, <br /> because there will becolumns specific to both Apple and Orange; if I <br /> include these columns in Fruit, then if Fruit is an Orange, theApple <br /> columns will be needlessly present in Apple rows. <br /><br /> The different ways of implementing this schemethat I've thought of (some <br /> uglier than others): <br /><br /> - Have Fruit contain foreign keys to both Appleand Orange, and write a <br /> check constraint in Fruit specifying that exactly one of (Apple FK, <br /> Orange FK)needs to be non-null. The disadvantage of this method is that <br /> it isn't exactly loosely coupled. For every otherfruit type table I <br /> implemented I'd have to go back and add a foreign key in Fruit. <br /><br /> - Have a foreignkey in Apple to Fruit, and in Orange to Fruit; then <br /> somehow create a constraint that imposes uniqueness onthe union of <br /> foreign keys in both Apple and Orange. To figure out what type of fruit a <br /> Fruit row is, runa query for foreign keys in Orange and Apple matching <br /> the primary key of Fruit. You'd also want to somehow createa constraint <br /> that the result of this query should always return exactly one row <br /> (perhaps with a trigger?)<br /><br /> Any advice will be appreciated! As I'm relatively new to Postgre, I might <br /> need some help withthe actual implementation as well. <br /><br /> Thank you. <br /><br /> - Greg <br /></blockquote><br /><br /> ---------------------------(endof broadcast)--------------------------- <br /> TIP 6: explain analyze is your friend <br/></blockquote><br />
В списке pgsql-sql по дате отправления: