Suggestions for schema design?

From: cluster <skrald(at)amossen(dot)dk>
To: pgsql-general(at)postgresql(dot)org
Subject: Suggestions for schema design?
Date: 2008-02-20 19:02:41
Message-ID: fphtgh$2uba$1@news.hub.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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!

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Douglas McNaught 2008-02-20 19:12:09 Re: Vacuous errors in pg_dump ... | pg_restore pipeline
Previous Message Kynn Jones 2008-02-20 19:02:14 Vacuous errors in pg_dump ... | pg_restore pipeline