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
