inheritance/foreignkeys

From: jef peeraer <jef(dot)peeraer(at)pandora(dot)be>
To: pgsql-general(at)postgresql(dot)org
Subject: inheritance/foreignkeys
Date: 2005-12-07 10:26:48
Message-ID: 4396B8E8.4060107@pandora.be
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

i have a project where inheritance could be used, i think. but after
going through the lists and having read the FM's, i realize that i beter
use another approach. i just describe the project in short :

users have to fill in forms, that mainly exists of different lists
and/or textboxes. Users are part of teams ( kind of groups) , and every
team is allowed to have its extra listitems, which should be added to
the global lists ( as i said, this could be solved with inheritance...).
The teams should be able to insert their extra listitems in a table,
without touching the global listitems. This could be solved by using a
different schema for each team, and each team has its own listitems
table. The selected items will end up in a registration table, also
specific per team.

public.listitems
id serial
id_up int
priority int2
label text

teamX.listitems
like public.listitems

teamY.listitems
like public.listitems

teamX.registration
reg_id serial
time_registration date
list_id foreign ....

i can create the composed listitems for a team by using a select and
UNION of the two tables. I am a little bit stuck how i should implement
the foreign key in the registration table as the list_id can reference a
listitem of the public schema as well of the teams schema.

1-maybe i should use triggers, and all listiteems inserts by the teams
should be inserted in the public.listitems as well
2- create a new table that consists of union of both tables
3- other solution

jef peeraer

Browse pgsql-general by date

  From Date Subject
Next Message Adam Witney 2005-12-07 10:44:25 Error on PostgreSQL mailing list web pages?
Previous Message Sim Zacks 2005-12-07 10:11:22 pg_autovacuum