Re: Suggestions for schema design? - Mailing list pgsql-general

cluster wrote:
> 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) ...

I think you'd better decide now if you want to let a transfer occur
without any corresponding event. That might be a recipe for pain.


> 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?
>

CREATE TABLE transfer_events (
   id SERIAL NOT NULL PRIMARY KEY,
   -- shared columns
);
CREATE TABLE transfer_events_a (
   integer NOT NULL,
   ...
) INHERITS transfer_events;

CREATE TABLE transfer_events_b (
   integer NOT NULL,
   ...
) INHERITS transfer_events;

CREATE TABLE transfer_events_c (
   integer NOT NULL,
   ...
) INHERITS transfer_events;

CREATE TABLE transfers (
   id SERIAL NOT NULL PRIMARY KEY,
   -- put the foreign key in transfers because it's the event
   -- that causes the transfer, not vice versa
   transfer_event_id integer NOT NULL
   ...
);

ALTER TABLE transfer_events_a ALTER COLUMN id
SET DEFAULT nextval('transfer_events_id_seq');
CREATE UNIQUE INDEX transfer_events_a_pk ON transfer_events_a (id);

ALTER TABLE transfer_events_b ALTER COLUMN id
SET DEFAULT nextval('transfer_events_id_seq');
CREATE UNIQUE INDEX transfer_events_b_pk ON transfer_events_b (id);

ALTER TABLE transfer_events_c ALTER COLUMN id
SET DEFAULT nextval('transfer_events_id_seq');
CREATE UNIQUE INDEX transfer_events_c_pk ON transfer_events_c (id);

ALTER TABLE ONLY transfers ADD CONSTRAINT fk_transfers_transfer_events
FOREIGN KEY (transfer_event_id)
REFERENCES transfer_events (id) ON DELETE CASCADE;


This allows one to INSERT directly into any of the inheriting tables
without specifying an ID. The child table will pick up the nextval()
properly, ensuring that all of the child table IDs will be unique. This,
then, is passed to the transfers table as the FK.

To get all events for some criteria and have some indication of which
child table a row is from:

SELECT te.id, te.created, pgc.relname
FROM transfer_events AS te, pg_class AS pgc
WHERE te.tableoid = pgc.oid
AND ...


1 | 2008-02-20 14:56:14.194147-05 | transfer_events_a
2 | 2008-02-20 14:56:14.194147-05 | transfer_events_b
3 | 2008-02-20 14:56:14.194147-05 | transfer_events_a
etc.


Go over the docs on inheritance carefully, though. There are a bunch of
limitations to inheritance (and some would say to avoid it, altogether).

http://www.postgresql.org/docs/8.3/interactive/ddl-inherit.html


b