pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedInheritance or no inheritance, there is a question
4+ messages / 3 participants
[nested] [flat]
* Inheritance or no inheritance, there is a question
@ 2003-08-19 03:59 Vernon Smith <vwu98034@lycos.com>
0 siblings, 1 reply; 4+ messages in thread
From: Vernon Smith @ 2003-08-19 03:59 UTC (permalink / raw)
To: pgsql-sql
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 profile table while the role B still has the whole profile table. In regarding of the DB scheme design, that should be an ideal 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, associated with 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, nor xml 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Inheritance or no inheritance, there is a question
@ 2003-08-19 04:36 Josh Berkus <josh@agliodbs.com>
parent: Vernon Smith <vwu98034@lycos.com>
0 siblings, 1 reply; 4+ messages in thread
From: Josh Berkus @ 2003-08-19 04:36 UTC (permalink / raw)
To: vwu98034@lycos.com; pgsql-sql
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.
--
Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Inheritance or no inheritance, there is a question
@ 2003-08-20 00:12 Josh Berkus <josh@agliodbs.com>
0 siblings, 0 replies; 4+ messages in thread
From: Josh Berkus @ 2003-08-20 00:12 UTC (permalink / raw)
To: vwu98034@lycos.com; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Inheritance or no inheritance, there is a question
@ 2003-08-20 15:11 David Fetter <shackle@fetter.org>
parent: Josh Berkus <josh@agliodbs.com>
0 siblings, 0 replies; 4+ messages in thread
From: David Fetter @ 2003-08-20 15:11 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2003-08-20 15:11 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-08-19 03:59 Inheritance or no inheritance, there is a question Vernon Smith <vwu98034@lycos.com>
2003-08-19 04:36 ` Josh Berkus <josh@agliodbs.com>
2003-08-20 15:11 ` David Fetter <shackle@fetter.org>
2003-08-20 00:12 Re: Inheritance or no inheritance, there is a question Josh Berkus <josh@agliodbs.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