Hello, and thank you to Steven and everyone else that submitted input
on this issue.
After reading a few more methods of doing things, I went with the
simplest one, as 1. time is of the essence, and 2. I'm stuck with
PostgreSQL 7.1 on the server I have to develop for.
I set the primary key of the parent class to a serial. Children have an
integer column with constraints as the primary key and foreign key to
the parent primary key column.
Thanks again,
- Greg
Steve Midgley wrote:
Hi Greg,
While not in a C++ framework, you might find 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:
http://wiki.rubyonrails.org/rails/pages/SingleTableInheritance
It's based on Martin Fowler's Patterns of Enterprise Architecture book
- please find references to his original patterns here:
http://www.martinfowler.com/eaaCatalog/singleTableInheritance.html
The key, I believe, is simply adding a "type" and a "parent_id" to the
"class" table, so you can model all your types and their hierarchical
relations. Fowler's diagram is pretty clear. I think then you would
store the data in another table (or tables) and link into this
inheritance structure to establish ancestry for any piece of data (some
people try to store the data in this table too, but I think that's a
mistake personally).
If I understand what you're trying to do, you can use this design
pattern in your application language to implement an inheritance scheme
without any special database features (i.e. in a SQL-standard manner).
I hope this is helpful,
Steve
At 12:28 AM 3/9/2007, [EMAIL PROTECTED] wrote:
Date: Thu, 08 Mar 2007 13:01:51 -0500
From: Greg Toombs <[EMAIL PROTECTED]>
To: [email protected]
Subject: A form of inheritance with PostgreSQL
Message-ID: <[EMAIL PROTECTED]>
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
---(end of
broadcast)---
TIP 6: explain analyze is your friend
