Received: from localhost (maia-5.hub.org [200.46.204.182])
	by postgresql.org (Postfix) with ESMTP id 1FE929FB41F
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Thu,  8 Mar 2007 15:06:14 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.182]) (amavisd-new, port 10024)
	with ESMTP id 01649-09 for <pgsql-sql-postgresql.org@postgresql.org>;
	Thu,  8 Mar 2007 15:06:02 -0400 (AST)
X-Greylist: delayed 01:04:25.158175 by SQLgrey-1.7.4
Received: from mi0.bluebottle.com (mi0.bluebottle.com [206.188.25.15])
	by postgresql.org (Postfix) with ESMTP id DBFCD9FBC63
	for <pgsql-sql@postgresql.org>; Thu,  8 Mar 2007 15:06:03 -0400 (AST)
Received: from fe0.bluebottle.com (internal.bluebottle.com [206.188.24.43])
	by mi0.bluebottle.com (8.13.1/8.13.1) with ESMTP id l28I1buh002710
	(version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=NO)
	for <pgsql-sql@postgresql.org>; Thu, 8 Mar 2007 10:01:37 -0800
DomainKey-Signature: a=rsa-sha1; s=mail; d=bluebottle.com; c=simple; q=dns;
	h=received:message-id:date:from:user-agent:mime-version:to:
	subject:x-enigmail-version:content-type:content-transfer-encoding;
	b=VUNY0pEFmQYJLTkBJ83WLcfC/udJo2fTgxrZQg6OqJ3ZZSaWVQTv+PePL+tb58cMY
	dKZoTDSk0+AWoQdSfs7h94B+KOkWptEGx8CELQpaQbqDMQFzQiyzHmbTM6wze0u
Received: from [10.0.0.4] (bas3-kitchener06-1096649828.dsl.bell.ca
	[65.93.140.100]) (authenticated bits=0)
	by fe0.bluebottle.com (8.13.1/8.13.1) with ESMTP id l28I1WB4013839
	(version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=NO)
	for <pgsql-sql@postgresql.org>; Thu, 8 Mar 2007 10:01:37 -0800
Message-ID: <45F04F8F.8030800@bluebottle.com>
Date: Thu, 08 Mar 2007 13:01:51 -0500
From: Greg Toombs <greg.toombs@bluebottle.com>
User-Agent: Thunderbird 1.5.0.10 (Windows/20070221)
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: A form of inheritance with PostgreSQL
X-Enigmail-Version: 0.94.2.0
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: ClamAV version 0.88.4,
	clamav-milter version 0.88.4 on localhost
X-Virus-Status: Clean
X-Trusted-Delivery: <c0dfe216f0118e22a90d7810fe6ed7dc>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=1.353 tagged_above=0 required=5 tests=BAYES_50,
	HTML_10_20, HTML_MESSAGE, MIME_HTML_ONLY, SPF_PASS
X-Spam-Level: *
X-Archive-Number: 200703/82
X-Sequence-Number: 27720

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html;charset=UTF-8" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
Hello.<br>
<br>
I'm trying to figure out how to nicely implement a C++ class-like
system with PostgreSQL. Consider the following:<br>
<br>
Tables Fruit, Apple, Orange<br>
<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.<br>
<br>
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.<br>
<br>
The different ways of implementing this scheme that I've thought of
(some uglier than others):<br>
<br>
- 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.<br>
<br>
- 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?)<br>
<br>
Any advice will be appreciated! As I'm relatively new to Postgre, I
might need some help with the actual implementation as well.<br>
<br>
Thank you.<br>
<br>
- Greg<br>
</body>
</html>