From: Michael A Nachbaur <mike@nachbaur.com>
To: pgsql-sql@postgresql.org
Subject: Object-Relational table design question
Date: Tue, 17 Jun 2003 08:57:26 -0700
User-Agent: KMail/1.5
MIME-Version: 1.0
Content-Type: text/plain;
  charset="us-ascii"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200306170857.26558.mike@nachbaur.com>

Hello everyone.

I'm re-designing an ISP billing / customer tracking system, and I am confused 
a bit about object-relational tables.  PostgreSQL is the first ORDBMS I've 
used and, though I've been using it for about two years now, I have never 
taken advantage of it's inheritance functionality.

(BTW, if this isn't the correct forum to post this in, please let me know.)

I'm trying to represent a set of services as a series of database tables; all 
service "classes" will have similar data -- base price, setup price, name, 
product code, etc -- but some will have more specific information.  For 
instance, a dial-up account may be restricted by the number of hours 
available, and then there may be an additional fee for overage.  A website 
account may be limited to disk space, monthly bandwidth quotas, etc.

I thought of defining the different services in their tables, all inherited 
from the base "Service" table, and then insert rows for the different 
services of each (for instance "Basic Webhosting", "Advanced Webhosting", 
etc).  I'm uncertain how much mileage I'll get with this approach however.

When querying for all services a customer is subscribed to, would I be able to 
have it return -- on a row-by-row basis -- the separate columns of each 
sub-table even if that row isn't available for another record? (sort of like 
a left outer join would be; or would I be better off just doing a plain-ol' 
left outer join across all my inherited service tables?)

Thanks in advance.  I'd appreciate any feedback you have to offer.

-- 
Michael A Nachbaur <mike@nachbaur.com>