refential integrity to multiple tables ??

[Nagib Abi Fadel]

HI,

let's say i have a tansaction table called TRANSACTION (transaction_id,amount,type,type_id)

Let's say a transaction can have multiple types: TYPE1, TYPE2 for example.

EACH type has his own definition and his own table.

Every transaction has a type that could be type1 or type2 that's why if the type is TYPE1 i want to make a referential integrity to the TYPE1_TABLE and if the type is TYPE2 i want to make a referential integrity to the TYPE2_TABLE.

IS IT POSSIBLE TO DO THAT???

I made a turn around to this problem by creating two tables:
- table TYPE1_TRANSACTION (type1_id,transaction_id)
- table TYPE2_TRANSACTION (type2_id,transaction_id)

But this does not seem so right for me ??

thx for any help


---------------------------------
Do you Yahoo!?
The New Yahoo! Shopping - with improved product search

[Erik Ronström]

Almost everything is possible if you accept to write your own triggers
and trigger functions. But there is no standard solution to this
problem.

There are of course inherited tables, but in the current
implementation, foreign key constraints doesn't work very well with
them (making them completly worthless IMO). That is going to change in
a future release according to the docs, but for now, table inheritance
is not the way to go in your case. Triggers are.

Regards
Erik

let's say i have a tansaction table called TRANSACTION
(transaction_id,amount,type,type_id)

Let's say a transaction can have multiple types: TYPE1, TYPE2 for
example.

EACH type has his own definition and his own table.

Every transaction has a type that could be type1 or type2 that's why
if the type is TYPE1 i want to make a referential integrity to the
TYPE1_TABLE and if the type is TYPE2 i want to make a referential
integrity to the TYPE2_TABLE.

IS IT POSSIBLE TO DO THAT???

I made a turn around to this problem by creating two tables:
- table TYPE1_TRANSACTION (type1_id,transaction_id)
- table TYPE2_TRANSACTION (type2_id,transaction_id)

But this does not seem so right for me ??

thx for any help

__________________________________________________ ______________________
Want to chat instantly with your online friends? Get the FREE Yahoo!
Messenger http://mail.messenger.yahoo.co.uk

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Richard Huxton]

On Wednesday 08 October 2003 06:53, Nagib Abi Fadel wrote:

HI,

let's say i have a tansaction table called TRANSACTION
(transaction_id,amount,type,type_id)

Let's say a transaction can have multiple types: TYPE1, TYPE2 for example.

EACH type has his own definition and his own table.

Every transaction has a type that could be type1 or type2 that's why if the
type is TYPE1 i want to make a referential integrity to the TYPE1_TABLE and
if the type is TYPE2 i want to make a referential integrity to the
TYPE2_TABLE.

IS IT POSSIBLE TO DO THAT???

You're looking at it the wrong way around, but in any case there are still
problems.

transaction_core(trans_id, trans_name, trans_type)
transaction_type1(tt1_core_id, tt1_extra1, tt1_extra2...)
transaction_type2(tt2_core_id, tt2_extra1, tt2_extra2...)

And have tt1_core reference trans_id (not the other way around). Do the same
for tt2_core and we can guarantee that the two transaction types refer to a
valid trans_id in transaction_core.

Now, what gets trickier is to specify that tt1_core should refer to a row in
transaction_core where trans_type=1.
Ideally, we could have a foreign-key to a view, or specify a constant in the
FK definition. We can't so you have to repeat the type field in
transaction_type1/2 and keep it fixed for every row.

HTH
--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Nagib Abi Fadel]

--- Richard Huxton <de*@archonet.com> wrote:

On Wednesday 08 October 2003 06:53, Nagib Abi Fadel
wrote:

HI,

let's say i have a tansaction table called

TRANSACTION

(transaction_id,amount,type,type_id)

Let's say a transaction can have multiple types:

TYPE1, TYPE2 for example.

EACH type has his own definition and his own

table.

Every transaction has a type that could be type1

or type2 that's why if the

type is TYPE1 i want to make a referential

integrity to the TYPE1_TABLE and

if the type is TYPE2 i want to make a referential

integrity to the

TYPE2_TABLE.

IS IT POSSIBLE TO DO THAT???

You're looking at it the wrong way around, but in
any case there are still
problems.

transaction_core(trans_id, trans_name, trans_type)
transaction_type1(tt1_core_id, tt1_extra1,
tt1_extra2...)
transaction_type2(tt2_core_id, tt2_extra1,
tt2_extra2...)

And have tt1_core reference trans_id (not the other
way around). Do the same
for tt2_core and we can guarantee that the two
transaction types refer to a
valid trans_id in transaction_core.

Now, what gets trickier is to specify that tt1_core
should refer to a row in
transaction_core where trans_type=1.
Ideally, we could have a foreign-key to a view, or
specify a constant in the
FK definition. We can't so you have to repeat the
type field in
transaction_type1/2 and keep it fixed for every row.

HTH
--
Richard Huxton
Archonet Ltd

Actually a type1_id can have mutiple corresponding
transaction_ids (same thing for type2) that's why i
created the tables as follows:

create table transaction(
transaction_id serial P K,
amount int,...)

create table TABLE_TYPE1(
type1_id serial P K,
....
)

create table transaction_type1(
type1_id int,
transaction_id int
)
for example we can have the following possible entries
in table transaction_type1:
type1_id,transaction_id
100,101
100,102
100,103
200,312
200,313
200,314
200,315

Same thing for type 2.

I can also add that a transaction id can be of type1
or (exclusive) of type2 and never of two types at the
same time.


__________________________________
Do you Yahoo!?
The New Yahoo! Shopping - with improved product search
http://shopping.yahoo.com

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)