pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedObject-Relational table design question
5+ messages / 3 participants
[nested] [flat]
* Object-Relational table design question
@ 2003-06-17 15:57 Michael A Nachbaur <mike@nachbaur.com>
2003-06-17 16:23 ` Re: Object-Relational table design question Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 5+ messages in thread
From: Michael A Nachbaur @ 2003-06-17 15:57 UTC (permalink / raw)
To: pgsql-sql
Hello everyone.
I'm re-designing an ISP billing / customer tracking system, and I am confused
a bit about object-relational tables. PostgreSQL is the first ORDBMS I've
used and, though I've been using it for about two years now, I have never
taken advantage of it's inheritance functionality.
(BTW, if this isn't the correct forum to post this in, please let me know.)
I'm trying to represent a set of services as a series of database tables; all
service "classes" will have similar data -- base price, setup price, name,
product code, etc -- but some will have more specific information. For
instance, a dial-up account may be restricted by the number of hours
available, and then there may be an additional fee for overage. A website
account may be limited to disk space, monthly bandwidth quotas, etc.
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?)
Thanks in advance. I'd appreciate any feedback you have to offer.
--
Michael A Nachbaur <mike@nachbaur.com>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Object-Relational table design question
2003-06-17 15:57 Object-Relational table design question Michael A Nachbaur <mike@nachbaur.com>
@ 2003-06-17 16:23 ` Josh Berkus <josh@agliodbs.com>
2003-06-18 13:20 ` Re: Object-Relational table design question Tm <x@Vex.Net>
0 siblings, 1 reply; 5+ messages in thread
From: Josh Berkus @ 2003-06-17 16:23 UTC (permalink / raw)
To: Michael A Nachbaur <mike@nachbaur.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Object-Relational table design question
2003-06-17 15:57 Object-Relational table design question Michael A Nachbaur <mike@nachbaur.com>
2003-06-17 16:23 ` Re: Object-Relational table design question Josh Berkus <josh@agliodbs.com>
@ 2003-06-18 13:20 ` Tm <x@Vex.Net>
2003-06-18 15:07 ` Re: Object-Relational table design question Josh Berkus <josh@agliodbs.com>
2003-06-18 15:49 ` Re: Object-Relational table design question Michael A Nachbaur <mike@nachbaur.com>
0 siblings, 2 replies; 5+ messages in thread
From: Tm @ 2003-06-18 13:20 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; Michael A Nachbaur <mike@nachbaur.com>; pgsql-sql
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)
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Object-Relational table design question
2003-06-17 15:57 Object-Relational table design question Michael A Nachbaur <mike@nachbaur.com>
2003-06-17 16:23 ` Re: Object-Relational table design question Josh Berkus <josh@agliodbs.com>
2003-06-18 13:20 ` Re: Object-Relational table design question Tm <x@Vex.Net>
@ 2003-06-18 15:07 ` Josh Berkus <josh@agliodbs.com>
1 sibling, 0 replies; 5+ messages in thread
From: Josh Berkus @ 2003-06-18 15:07 UTC (permalink / raw)
To: Tm <x@Vex.Net>; Michael A Nachbaur <mike@nachbaur.com>; pgsql-sql
Tm,
> 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.
Yeah, that's a very good approach. I use it for any client where they need
to be able to add new "attributes" and services after the system is built.
It also works for other things ... for example, a "skills" list for an HR
database.
--
Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Object-Relational table design question
2003-06-17 15:57 Object-Relational table design question Michael A Nachbaur <mike@nachbaur.com>
2003-06-17 16:23 ` Re: Object-Relational table design question Josh Berkus <josh@agliodbs.com>
2003-06-18 13:20 ` Re: Object-Relational table design question Tm <x@Vex.Net>
@ 2003-06-18 15:49 ` Michael A Nachbaur <mike@nachbaur.com>
1 sibling, 0 replies; 5+ messages in thread
From: Michael A Nachbaur @ 2003-06-18 15:49 UTC (permalink / raw)
To: Tm <x@Vex.Net>; Josh Berkus <josh@agliodbs.com>; pgsql-sql
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>
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2003-06-18 15:49 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-06-17 15:57 Object-Relational table design question Michael A Nachbaur <mike@nachbaur.com>
2003-06-17 16:23 ` Josh Berkus <josh@agliodbs.com>
2003-06-18 13:20 ` Tm <x@Vex.Net>
2003-06-18 15:07 ` Josh Berkus <josh@agliodbs.com>
2003-06-18 15:49 ` Michael A Nachbaur <mike@nachbaur.com>
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