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