From: Josh Berkus <josh@agliodbs.com>
To: Michael A Nachbaur <mike@nachbaur.com>
To: pgsql-sql@postgresql.org
Subject: Re: Object-Relational table design question
Date: Tue, 17 Jun 2003 09:23:34 -0700
Message-ID: <200306170923.34974.josh@agliodbs.com> (raw)
In-Reply-To: <200306170857.26558.mike@nachbaur.com>
References: <200306170857.26558.mike@nachbaur.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: josh@agliodbs.com, mike@nachbaur.com
Subject: Re: Object-Relational table design question
In-Reply-To: <200306170923.34974.josh@agliodbs.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox