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