Re: Object-Relational table design question

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.serviceLEFT 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