<!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>