Postgres Pro
Downloads
Home   >   mailing lists

Thread: Inheritance or no inheritance, there is a question

Inheritance or no inheritance, there is a question

From
"Vernon Smith"
Date:

I am working on an application system refinement. There is a user profile table in the current system. After the
refinement,there are new separated roles, A and B, of the users. The role A only has a few valid fields of the original
profiletable while the role B still has the whole profile table. In regarding of the DB scheme design, that should be
anideal case of using inheritance. I can have something like
 

table PROFILE_A ( 
ID int – PK,
...
):

table PROFILE_B {
...
inherits (A)
);

However, there is a problem. There are some multi-valued field tables, such as languages the person can speak,
associatedwith the profile table referred by ID in the profile table. These tables are still needed for the A and B.
But,the ID is not accessible from the PROFILE_B. 
 

What is the best solution for this DB scheme problem? 

p.s. I can't use array data type for the multi-valued fields since they need to be workable with a selection statement,
norxml or comma separated format for maintainablity concern. 
 

Thanks,

v.


____________________________________________________________
Get advanced SPAM filtering on Webmail or POP Mail ... Get Lycos Mail!
http://login.mail.lycos.com/r/referral?aid=27005

Re: Inheritance or no inheritance, there is a question

From
Josh Berkus
Date:
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 Aid Primary Keydetail1detail2

Profile Bid Primary Key references Profile A ( ID )detail 3detail 4detail 5

Profile Languagesid not null references profile A ( ID )language idprimary 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.

-- 
Josh Berkus
Aglio Database Solutions
San Francisco

Re: Inheritance or no inheritance, there is a question

From
Josh Berkus
Date:
Vernon,

> Thanks for your suggestion. I haven't thought this structure, to compose a
> table with another one. That probably is the best solution I can have. With
> this approach, I need to have two tables for B. As a result, all queries,
> insertion, update, deletion, of B need to operate on these two tables. Is
> it possible using a view to them as one table?

Yes.  In fact, by configuring PostgreSQL's RULE system, you can make the view 
updatable, insertable and deleteable as well.  See the online docs under 
"CREATE RULE" and "Server-side Programming"

-- 
Josh Berkus
Aglio Database Solutions
San Francisco

Re: Inheritance or no inheritance, there is a question

From
David Fetter
Date:
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