I really need some input:
In a system that handles money transfers I have a table to store each
money transfer. A number of different events can result in a money
transfer but the events are so different that each event type is stored
in its own table.
So we have a schema of the form:
TRANSFERS (TRANSFER_ID, col2, col3, col4, ...)
EVENT_TYPE_A (EVENT_A_ID, TRANSFER_ID, col3, col4, ...)
EVENT_TYPE_B (EVENT_B_ID, TRANSFER_ID, col3, col4, ...)
EVENT_TYPE_C (EVENT_C_ID, TRANSFER_ID, col3, col4, ...)
...
With this design it is easy to map a specific event to the corresponding
transfer (if any). However, if I want to create a list of transfers and
for each transfer also give the corresponding event ID (if any) the only
way is to "left join" *all* the EVENT-tables with the TRANSFERS table.
This is slow.
Can I modify the design to make a more direct link between transfers and
events?
Of course I could add EVENT_A_ID, EVENT_B_ID, etc. to the TRANSFERS
table but I think this would soil the otherwise clean TRANSFERS table.
What do you think?
One could also introduce a third table:
TRANSFER_EVENTS(TRANSFER_ID, EVENT_A_ID, EVENT_B_ID, ...)
which only results in the need of a single join operation to create the
list but adds an INPUT statement to the complexity.
Any ideas? What would you do?
Thanks!
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your
message can get through to the mailing list cleanly
