Re: refential integrity to multiple tables ??

--- Richard Huxton <dev(at)archonet(dot)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