From: Tm <x@Vex.Net>
To: 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: Wed, 18 Jun 2003 09:20:44 -0400
Message-ID: <200306180920.44304.x@Vex.Net> (raw)
In-Reply-To: <200306170923.34974.josh@agliodbs.com>
References: <200306170857.26558.mike@nachbaur.com>
<200306170923.34974.josh@agliodbs.com>
On June 17, 2003 12:23 pm, Josh Berkus wrote:
> Personally, I'm not a fan of inherited tables; I think they muddy up
> the relationality of SQL without providing any additional
We actually are doing what the original poster is in the process of
doing; we have an ISP billing system based on postgresql. I have to
agree with the above. We actually did use inheritence for a few things
(though not for account definitions), and I've always found it somewhat
of a pain. Especially when pg_dump was broken and corrupted the
database on restore... I believe this is fixed now, but I can't see the
benefit of the complication, whereas it does make the relationships
more murky as described above.
> JOIN webhosting ON service.id = webhosting.service
This would work though it's not very scaleable. Our current system makes
all elements of a service into what we call an 'attribute'. The
attributes are defined in a table, and attached to each account type,
and turned on or off, and twiddled with various definitions such as
term/period billing, etc. This makes it relatively easy to add new
services... just add another entry in the account attributes table,
whereas with hard coded joins above, if you add more services you're
going to have to edit all of your code where joins take place.
So the billing job, for example, if you want a list of services that a
customer's account has:
SELECT * FROM account_attribute
WHERE account_type=customer.account_type
AND bill_mode>0;
(We go even further and do resource based accounting in yet another
relation which references the attributes... it's a bit complicated, but
I think its proving quite flexible so far, and cleaner than using
inheritance).
--
Tim Middleton | Cain Gang Ltd | But the trouble was that my hysterical
fit
x@veX.net | www.Vex.Net | could not go on for ever. --Dost (NFTU)
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: x@Vex.Net, josh@agliodbs.com, mike@nachbaur.com
Subject: Re: Object-Relational table design question
In-Reply-To: <200306180920.44304.x@Vex.Net>
* 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