From: Michael A Nachbaur <mike@nachbaur.com>
To: Tm <x@Vex.Net>
To: Josh Berkus <josh@agliodbs.com>
To: pgsql-sql@postgresql.org
Subject: Re: Object-Relational table design question
Date: Wed, 18 Jun 2003 08:49:04 -0700
Message-ID: <200306180849.04187.mike@nachbaur.com> (raw)
In-Reply-To: <200306180920.44304.x@Vex.Net>
References: <200306170857.26558.mike@nachbaur.com>
<200306170923.34974.josh@agliodbs.com>
<200306180920.44304.x@Vex.Net>
On Wednesday 18 June 2003 06:20 am, Tm wrote:
> 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.
Yeah, the inheritance support did seem to be a little funky, especially with
the indexing problems mentioned in the documentation.
> > 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.
How scalable would this be? If every time you want to get details on a
customer you had to do a join across several tables, multiple records of
which would be combined into the same result, what kind of hit would you
sustain if you had a large number of customers, attributes and/or users
accessing the system?
> 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).
I did something to this effect years ago on an Oracle database, but since I
was just a newbie to SQL, I assumed there had to be a better way of doing
this. :-) Beginners luck?
--
Michael A Nachbaur <mike@nachbaur.com>
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: mike@nachbaur.com, x@Vex.Net, josh@agliodbs.com
Subject: Re: Object-Relational table design question
In-Reply-To: <200306180849.04187.mike@nachbaur.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