Re: Object-Relational table design question

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(at)nachbaur(dot)com>