From: David Fetter <shackle@fetter.org>
To: pgsql-sql@postgresql.org
Subject: Re: Inheritance or no inheritance, there is a question
Date: Wed, 20 Aug 2003 10:11:32 -0500
Message-ID: <-HOdnWKu_705Et6iXTWc-g@speakeasy.net> (raw)
References: <GMCPCMGLPNDDCFAA@mailcity.com>
<200308182136.39919.josh@agliodbs.com>
Josh Berkus <josh@agliodbs.com> wrote:
> Vernon,
>
>> What is the best solution for this DB scheme problem?
>
> Have you considered not using inheritance? As a relational-SQL geek myself,
> I'm not keen on inheritance -- I feel it mucks up the relational model. Not
> everyone agrees with me, of course.
>
> Personally, I'd suggest the following structure:
>
> Profile A
> id Primary Key
> detail1
> detail2
>
> Profile B
> id Primary Key references Profile A ( ID )
> detail 3
> detail 4
> detail 5
>
> Profile Languages
> id not null references profile A ( ID )
> language id
> primary key id, language id
>
> etc.
>
> In this way, Profile B is a child table with a 1:0-1 relationship
> with Profile A. Multi-value dependancies, like Languages, can be
> related to either the people who belong to the B group (and, by
> implication, the B group) or the people who belong to the A group
> only.
>
> Want the B group? SELECT A JOIN B
> Want the A group only? SELECT A EXCEPT B
>
> This is the "relational" way to approach the problem.
Grewvy!
I've been running a system that takes various kinds of payments, some
tables of which are below. INSERTs & UPDATEs only happen on the
tables that inherit from the payment table. To sum up or otherwise do
reports, I SELECT from the payment table. Is there some relational
way to do this without ripping my hair out every time I want to do a
new query? As some of you know, I don't have much hair left to lose ;)
CREATE TABLE payment (
payment_id SERIAL NOT NULL PRIMARY KEY
, order_id INTEGER NOT NULL REFERENCES order(order_id)
ON DELETE RESTRICT
, amount INTEGER NOT NULL -- pennies
, payment_date DATE NOT NULL DEFAULT now()
);
CREATE TABLE payment_check (
check_no INTEGER NOT NULL
, payer_name VARCHAR(255) NOT NULL
) INHERITS (payment);
CREATE TABLE payment_money_order (
issuer VARCHAR(255) NOT NULL
, mo_num VARCHAR(64) NOT NULL
) INHERITS (payment);
CREATE TABLE payment_wire (
payment_wire_desc VARCHAR(255) NOT NULL
) INHERITS (payment);
Cheers,
D
--
David Fetter david@fetter.org http://fetter.org/
phone: +1 510 893 6100 cell: +1 415 235 3778
Fascism should more properly be called corporatism, since it is the
merger of state and corporate power.
Benito Mussolini
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: shackle@fetter.org
Subject: Re: Inheritance or no inheritance, there is a question
In-Reply-To: <-HOdnWKu_705Et6iXTWc-g@speakeasy.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