Suggestions for schema design?
От | cluster |
---|---|
Тема | Suggestions for schema design? |
Дата | |
Msg-id | fphtgh$2uba$1@news.hub.org обсуждение исходный текст |
Ответы |
Re: Suggestions for schema design?
|
Список | 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!
В списке pgsql-general по дате отправления: