Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id B5B469FB274 for ; Fri, 9 Mar 2007 04:28:28 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-new, port 10024) with ESMTP id 18227-10 for ; Fri, 9 Mar 2007 04:28:20 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from interscan.indicator.com (mail.indicator.be [193.121.199.3]) by postgresql.org (Postfix) with ESMTP id 4B04B9FA501 for ; Fri, 9 Mar 2007 04:28:21 -0400 (AST) Received: from webaccess.indicator.be ([10.100.1.7]) by interscan.indicator.com with InterScan Message Security Suite; Fri, 09 Mar 2007 09:15:41 +0100 Received: from DOMEXT-MTA by webaccess.indicator.bewith Novell_GroupWise; Fri, 09 Mar 2007 09:28:19 +0100 Message-Id: <45F128A3.A3DD.0030.0@indicator.be> X-Mailer: Novell GroupWise Internet Agent 7.0.1 Date: Fri, 09 Mar 2007 09:28:04 +0100 From: "Bart Degryse" To: Subject: Re: A form of inheritance with PostgreSQL References: <45F04F8F.8030800@bluebottle.com> In-Reply-To: <45F04F8F.8030800@bluebottle.com> Mime-Version: 1.0 Content-Type: multipart/alternative; boundary="=__Part4266FB84.1__=" X-imss-version: 2.046 X-imss-result: Passed X-imss-approveListMatch: *@indicator.be X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.316 tagged_above=0 required=5 tests=AWL, BAYES_50, FORGED_RCVD_HELO, HTML_MESSAGE, UNPARSEABLE_RELAY, UPPERCASE_25_50 X-Spam-Level: X-Archive-Number: 200703/96 X-Sequence-Number: 27734 --=__Part4266FB84.1__= Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: quoted-printable Just a little example of what I've been using on version 8.0.3 with total = satisfaction. =20 CREATE TABLE "public"."tblMovementDetails" ( "ID" INTEGER NOT NULL, "PlanningDetailID" INTEGER NOT NULL, "MovementID" INTEGER NOT NULL, "UserID" VARCHAR(5) NOT NULL, "Number" INTEGER DEFAULT 0 NOT NULL, "ChangeDate" DATE NOT NULL, CONSTRAINT "PK_tblMovementDetails" PRIMARY KEY("ID") ) WITHOUT OIDS; =20 CREATE RULE "disallow_delete" AS ON DELETE TO "public"."tblMovementDetails"= DO INSTEAD NOTHING; =20 CREATE RULE "disallow_insert" AS ON INSERT TO "public"."tblMovementDetails"= DO INSTEAD NOTHING; =20 CREATE RULE "disallow_update" AS ON UPDATE TO "public"."tblMovementDetails"= DO INSTEAD NOTHING; =20 CREATE TABLE "public"."tblDeliveryDetails" ( "InvoiceAmount" NUMERIC(8,2) DEFAULT 0 NOT NULL, CONSTRAINT "PK_tblDeliveryDetails" PRIMARY KEY("ID"), CONSTRAINT "CH_tblDeliveryDetails_InvoiceAmount" CHECK ("InvoiceAmount" = >=3D (0)::numeric), CONSTRAINT "CH_tblDeliveryDetails_Number" CHECK ("Number" >=3D 0), CONSTRAINT "FK_tblDeliveryDetails_tblMovement" FOREIGN KEY ("MovementID")= REFERENCES "public"."tblDelivery"("ID") ON DELETE NO ACTION ON UPDATE NO ACTION NOT DEFERRABLE, CONSTRAINT "FK_tblDeliveryDetails_tblPlanningDetails" FOREIGN KEY = ("PlanningDetailID") REFERENCES "public"."tblPlanningDetails"("ID") ON DELETE NO ACTION ON UPDATE NO ACTION NOT DEFERRABLE ) INHERITS ("public"."tblMovementDetails") WITHOUT OIDS; =20 CREATE INDEX "IDX_tblDeliveryDetails_PlanningDetailID" ON "public"."tblDeli= veryDetails" USING btree ("PlanningDetailID"); =20 CREATE TRIGGER "TRIG_tblDeliveryDetails_Archive" AFTER INSERT OR UPDATE OR = DELETE ON "public"."tblDeliveryDetails" FOR EACH ROW EXECUTE PROCEDURE "public"."func_archive_delivery"(); =20 CREATE TRIGGER "TRIG_tblDeliveryDetails_ChangeDate" BEFORE INSERT OR = UPDATE ON "public"."tblDeliveryDetails" FOR EACH ROW EXECUTE PROCEDURE "public"."func_set_changedate"(); =20 CREATE RULE "get_pkey_on_insert" AS ON INSERT TO "public"."tblDeliveryDetai= ls" DO (SELECT currval('"tblDeliveryDetails_ID_seq"'::text) AS "ID", new."Plann= ingDetailID";); =20 CREATE TABLE "public"."tblOrderDetails" ( "QuoteID" INTEGER NOT NULL, CONSTRAINT "PK_tblOrderDetails" PRIMARY KEY("ID"), CONSTRAINT "CH_tblOrderDetails_Number" CHECK ("Number" >=3D 0), CONSTRAINT "FK_tblOrderDetails_tblMovement" FOREIGN KEY ("MovementID") REFERENCES "public"."tblOrder"("ID") ON DELETE NO ACTION ON UPDATE NO ACTION NOT DEFERRABLE, CONSTRAINT "FK_tblOrderDetails_tblPlanningDetails" FOREIGN KEY ("Planning= DetailID") REFERENCES "public"."tblPlanningDetails"("ID") ON DELETE NO ACTION ON UPDATE NO ACTION NOT DEFERRABLE, CONSTRAINT "FK_tblOrderDetails_tblQuote" FOREIGN KEY ("QuoteID") REFERENCES "public"."tblQuote"("ID") ON DELETE NO ACTION ON UPDATE NO ACTION NOT DEFERRABLE ) INHERITS ("public"."tblMovementDetails") WITHOUT OIDS; =20 CREATE TRIGGER "TRIG_tblOrderDetails_ChangeDate" BEFORE INSERT OR UPDATE ON "public"."tblOrderDetails" FOR EACH ROW EXECUTE PROCEDURE "public"."func_set_changedate"(); =20 CREATE RULE "get_pkey_on_insert" AS ON INSERT TO "public"."tblOrderDetails"= DO (SELECT currval('"tblOrderDetails_ID_seq"'::text) AS "ID", new."Planning= DetailID";); =20 CREATE TABLE "public"."tblSendingDetails" ( "HandlingCode" VARCHAR(10) DEFAULT ''::character varying, CONSTRAINT "PK_tblSendingDetails" PRIMARY KEY("ID"), CONSTRAINT "CH_tblSendingDetails_Number" CHECK ("Number" >=3D 0), CONSTRAINT "FK_tblSendingDetails_tblMovement" FOREIGN KEY ("MovementID") REFERENCES "public"."tblSending"("ID") ON DELETE NO ACTION ON UPDATE NO ACTION NOT DEFERRABLE, CONSTRAINT "FK_tblSendingDetails_tblPlanningDetails" FOREIGN KEY = ("PlanningDetailID") REFERENCES "public"."tblPlanningDetails"("ID") ON DELETE NO ACTION ON UPDATE NO ACTION NOT DEFERRABLE ) INHERITS ("public"."tblMovementDetails") WITHOUT OIDS; =20 CREATE INDEX "IDX_tblSendingDetails_PlanningDetailID" ON "public"."tblSendi= ngDetails" USING btree ("PlanningDetailID"); =20 CREATE TRIGGER "TRIG_tblSendingDetails_Archive" AFTER INSERT OR UPDATE OR = DELETE ON "public"."tblSendingDetails" FOR EACH ROW EXECUTE PROCEDURE "public"."func_archive_sending"(); =20 CREATE TRIGGER "TRIG_tblSendingDetails_ChangeDate" BEFORE INSERT OR UPDATE ON "public"."tblSendingDetails" FOR EACH ROW EXECUTE PROCEDURE "public"."func_set_changedate"(); =20 CREATE RULE "get_pkey_on_insert" AS ON INSERT TO "public"."tblSendingDetail= s" DO (SELECT currval('"tblSendingDetails_ID_seq"'::text) AS "ID", new."Planni= ngDetailID";); >>> Greg Toombs 2007-03-08 19:01 >>> Hello. I'm trying to figure out how to nicely implement a C++ class-like system = with PostgreSQL. Consider the following: Tables Fruit, Apple, Orange I want to design the foreign key scheme such that there are relations = between fruit and apple, and fruit and orange, that imply that apple is a = fruit, and orange is a fruit. I don't want to eliminate the existence of Apple and Orange tables, = because there will be columns specific to both Apple and Orange; if I = include these columns in Fruit, then if Fruit is an Orange, the Apple = columns will be needlessly present in Apple rows. The different ways of implementing this scheme that I've thought of (some = uglier than others): - Have Fruit contain foreign keys to both Apple and Orange, and write a = check constraint in Fruit specifying that exactly one of (Apple FK, Orange = FK) needs to be non-null. The disadvantage of this method is that it isn't = exactly loosely coupled. For every other fruit type table I implemented = I'd have to go back and add a foreign key in Fruit. - Have a foreign key in Apple to Fruit, and in Orange to Fruit; then = somehow create a constraint that imposes uniqueness on the union of = foreign keys in both Apple and Orange. To figure out what type of fruit a = Fruit row is, run a query for foreign keys in Orange and Apple matching = the primary key of Fruit. You'd also want to somehow create a constraint = that the result of this query should always return exactly one row = (perhaps with a trigger?) Any advice will be appreciated! As I'm relatively new to Postgre, I might = need some help with the actual implementation as well. Thank you. - Greg --=__Part4266FB84.1__= Content-Type: text/html; charset=US-ASCII Content-Transfer-Encoding: quoted-printable Content-Description: HTML
Just a little example of what I've been using on version 8.0.3 with = total satisfaction.
 
CREATE TABLE "public"."tblMovementDetails" (
  "ID" INTEGER = NOT NULL,
  "PlanningDetailID" INTEGER NOT NULL,
  = "MovementID" INTEGER NOT NULL,
  "UserID" VARCHAR(5) NOT NULL,
&= nbsp; "Number" INTEGER DEFAULT 0 NOT NULL,
  "ChangeDate" DATE NOT = NULL,
  CONSTRAINT "PK_tblMovementDetails" PRIMARY KEY("ID")
) = WITHOUT OIDS;
 
CREATE RULE "disallow_delete" AS ON DELETE TO "public"."tblMovementDet= ails"
DO INSTEAD NOTHING;
 
CREATE RULE "disallow_insert" AS ON INSERT TO "public"."tblMovementDet= ails"
DO INSTEAD NOTHING;
 
CREATE RULE "disallow_update" AS ON UPDATE TO "public"."tblMovementDet= ails"
DO INSTEAD NOTHING;
 
CREATE TABLE "public"."tblDeliveryDetails" (
  "InvoiceAmount"= NUMERIC(8,2) DEFAULT 0 NOT NULL,
  CONSTRAINT "PK_tblDeliveryDetai= ls" PRIMARY KEY("ID"),
  CONSTRAINT "CH_tblDeliveryDetails_InvoiceA= mount" CHECK ("InvoiceAmount" >=3D (0)::numeric),
  CONSTRAINT = "CH_tblDeliveryDetails_Number" CHECK ("Number" >=3D 0),
  = CONSTRAINT "FK_tblDeliveryDetails_tblMovement" FOREIGN KEY ("MovementID")    REFERENCES "public"."tblDelivery"("ID")
  = ;  ON DELETE NO ACTION
    ON UPDATE NO ACTION
&n= bsp;   NOT DEFERRABLE,
  CONSTRAINT "FK_tblDeliveryDetail= s_tblPlanningDetails" FOREIGN KEY ("PlanningDetailID")
   = ; REFERENCES "public"."tblPlanningDetails"("ID")
    ON = DELETE NO ACTION
    ON UPDATE NO ACTION
  &= nbsp; NOT DEFERRABLE
) INHERITS ("public"."tblMovementDetails")
WITHO= UT OIDS;
 
CREATE INDEX "IDX_tblDeliveryDetails_PlanningDetailID" ON "public"."tb= lDeliveryDetails"
  USING btree ("PlanningDetailID");
 
CREATE TRIGGER "TRIG_tblDeliveryDetails_Archive" AFTER INSERT OR = UPDATE OR DELETE
ON "public"."tblDeliveryDetails" FOR EACH ROW
EXECUT= E PROCEDURE "public"."func_archive_delivery"();
 
CREATE TRIGGER "TRIG_tblDeliveryDetails_ChangeDate" BEFORE INSERT OR = UPDATE
ON "public"."tblDeliveryDetails" FOR EACH ROW
EXECUTE = PROCEDURE "public"."func_set_changedate"();
 
CREATE RULE "get_pkey_on_insert" AS ON INSERT TO "public"."tblDelivery= Details"
DO (SELECT currval('"tblDeliveryDetails_ID_seq"'::text) AS = "ID", new."PlanningDetailID";);
 
CREATE TABLE "public"."tblOrderDetails" (
  "QuoteID" INTEGER = NOT NULL,
  CONSTRAINT "PK_tblOrderDetails" PRIMARY KEY("ID"),
&= nbsp; CONSTRAINT "CH_tblOrderDetails_Number" CHECK ("Number" >=3D = 0),
  CONSTRAINT "FK_tblOrderDetails_tblMovement" FOREIGN KEY = ("MovementID")
    REFERENCES "public"."tblOrder"("ID")    ON DELETE NO ACTION
    ON UPDATE = NO ACTION
    NOT DEFERRABLE,
  CONSTRAINT = "FK_tblOrderDetails_tblPlanningDetails" FOREIGN KEY ("PlanningDetailID")    REFERENCES "public"."tblPlanningDetails"("ID")
 = ;   ON DELETE NO ACTION
    ON UPDATE NO = ACTION
    NOT DEFERRABLE,
  CONSTRAINT = "FK_tblOrderDetails_tblQuote" FOREIGN KEY ("QuoteID")
   = REFERENCES "public"."tblQuote"("ID")
    ON DELETE NO = ACTION
    ON UPDATE NO ACTION
    NOT = DEFERRABLE
) INHERITS ("public"."tblMovementDetails")
WITHOUT = OIDS;
 
CREATE TRIGGER "TRIG_tblOrderDetails_ChangeDate" BEFORE INSERT OR = UPDATE
ON "public"."tblOrderDetails" FOR EACH ROW
EXECUTE PROCEDURE = "public"."func_set_changedate"();
 
CREATE RULE "get_pkey_on_insert" AS ON INSERT TO "public"."tblOrderDet= ails"
DO (SELECT currval('"tblOrderDetails_ID_seq"'::text) AS "ID", = new."PlanningDetailID";);
 
CREATE TABLE "public"."tblSendingDetails" (
  "HandlingCode" = VARCHAR(10) DEFAULT ''::character varying,
  CONSTRAINT "PK_tblSend= ingDetails" PRIMARY KEY("ID"),
  CONSTRAINT "CH_tblSendingDetails_N= umber" CHECK ("Number" >=3D 0),
  CONSTRAINT "FK_tblSendingDetai= ls_tblMovement" FOREIGN KEY ("MovementID")
    REFERENCES= "public"."tblSending"("ID")
    ON DELETE NO ACTION
&= nbsp;   ON UPDATE NO ACTION
    NOT DEFERRABLE,=
  CONSTRAINT "FK_tblSendingDetails_tblPlanningDetails" FOREIGN = KEY ("PlanningDetailID")
    REFERENCES "public"."tblPlan= ningDetails"("ID")
    ON DELETE NO ACTION
  = ;  ON UPDATE NO ACTION
    NOT DEFERRABLE
) = INHERITS ("public"."tblMovementDetails")
WITHOUT OIDS;
 
CREATE INDEX "IDX_tblSendingDetails_PlanningDetailID" ON "public"."tbl= SendingDetails"
  USING btree ("PlanningDetailID");
 
CREATE TRIGGER "TRIG_tblSendingDetails_Archive" AFTER INSERT OR = UPDATE OR DELETE
ON "public"."tblSendingDetails" FOR EACH ROW
EXECUTE= PROCEDURE "public"."func_archive_sending"();
 
CREATE TRIGGER "TRIG_tblSendingDetails_ChangeDate" BEFORE INSERT OR = UPDATE
ON "public"."tblSendingDetails" FOR EACH ROW
EXECUTE = PROCEDURE "public"."func_set_changedate"();
 
CREATE RULE "get_pkey_on_insert" AS ON INSERT TO "public"."tblSendingD= etails"
DO (SELECT currval('"tblSendingDetails_ID_seq"'::text) AS "ID", = new."PlanningDetailID";);

>>> Greg Toombs <greg.toombs@b= luebottle.com> 2007-03-08 19:01 >>>
Hello.

I'm = trying to figure out how to nicely implement a C++ class-like system with = PostgreSQL. Consider the following:

Tables Fruit, Apple, Orange
<= BR>I want to design the foreign key scheme such that there are relations = between fruit and apple, and fruit and orange, that imply that apple is a = fruit, and orange is a fruit.

I don't want to eliminate the = existence of Apple and Orange tables, because there will be columns = specific to both Apple and Orange; if I include these columns in Fruit, = then if Fruit is an Orange, the Apple columns will be needlessly present = in Apple rows.

The different ways of implementing this scheme that = I've thought of (some uglier than others):

- Have Fruit contain = foreign keys to both Apple and Orange, and write a check constraint in = Fruit specifying that exactly one of (Apple FK, Orange FK) needs to be = non-null. The disadvantage of this method is that it isn't exactly loosely = coupled. For every other fruit type table I implemented I'd have to go = back and add a foreign key in Fruit.

- Have a foreign key in Apple = to Fruit, and in Orange to Fruit; then somehow create a constraint that = imposes uniqueness on the union of foreign keys in both Apple and Orange. = To figure out what type of fruit a Fruit row is, run a query for foreign = keys in Orange and Apple matching the primary key of Fruit. You'd also = want to somehow create a constraint that the result of this query should = always return exactly one row (perhaps with a trigger?)

Any advice = will be appreciated! As I'm relatively new to Postgre, I might need some = help with the actual implementation as well.

Thank you.

- = Greg
--=__Part4266FB84.1__=--