Content-Type: text/plain;
  charset="iso-8859-1"
From: Josh Berkus <josh@agliodbs.com>
Organization: Aglio Database Solutions
To: Michael A Nachbaur <mike@nachbaur.com>, pgsql-sql@postgresql.org
Subject: Re: Object-Relational table design question
Date: Tue, 17 Jun 2003 09:23:34 -0700
User-Agent: KMail/1.4.3
References: <200306170857.26558.mike@nachbaur.com>
In-Reply-To: <200306170857.26558.mike@nachbaur.com>
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
Message-Id: <200306170923.34974.josh@agliodbs.com>

Michael,

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

This is the right forum.

> 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?)

Personally, I'm not a fan of inherited tables; I think they muddy up the 
relationality of SQL without providing any additional functionality.  
However, my opinion is (obviously) not shared by everyone.

Were I building your database, I would instead do it "relationally" rather 
than using inheritance (hopefully the ASCII art will transmit ...)

Customer (name, id, phone, etc.)
     |  one
     |
     V  many
Service (type, price, payment method, sign-up date, etc.)
     |                      |                        |
     V                      V                       V 
Webhosting       Dial-up                  DSL
  Details            Details                  Details
     |    
     V                          
Advanced Details       

This sort of partitioning of data is what the relational model is *for*; using 
the above, you should be able to access as much, or as little, of each 
customer's service details as you wish with left outer joins or by 
sub-selecting on service type.  For example, to get webhosting details:

SELECT ... FROM customer JOIN service 
	ON (customer.id = service.customer AND service.type = 'web')
	JOIN webhosting ON service.id = webhosting.service
	LEFT OUTER JOIN web_advanced ON webhosting.id = web_advanced.webhosting

Which would give you all customer, service, and basic hosting details, plus 
advanced hosting details of there are any.

-- 
Josh Berkus
Aglio Database Solutions
San Francisco