From: Michael A Nachbaur <mike@nachbaur.com>
To: Tm <x@Vex.Net>, Josh Berkus <josh@agliodbs.com>, pgsql-sql@postgresql.org
Subject: Re: Object-Relational table design question
Date: Wed, 18 Jun 2003 08:49:04 -0700
User-Agent: KMail/1.5
References: <200306170857.26558.mike@nachbaur.com>
	<200306170923.34974.josh@agliodbs.com>
	<200306180920.44304.x@Vex.Net>
In-Reply-To: <200306180920.44304.x@Vex.Net>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200306180849.04187.mike@nachbaur.com>

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>