Re: Inheritance or no inheritance, there is a question

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