schema design question

From: snacktime <snacktime(at)gmail(dot)com>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: schema design question
Date: 2007-02-24 08:59:02
Message-ID: 1f060c4c0702240059s53a8f7a8ie241616351692329@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Say you have 8 different data models that are related enough to share
roughly 70% of the same fields, but the shared fields are not always
the same. And also within any given model, some fields can be empty.
The business logic is that data is pulled from all the data models and
put into a common format that has all the combined fields, and sent
over the wire as a transaction.

The models are different credit card transaction types. Most of them
share a lot of common fields, but each one is different by about 3-6
fields, and each one has different requirements on which fields can be
empty and what their default values are. In the past I've basically
used one table and modeled it after the message format that is sent to
the processor. I'm thinking about creating a table for each
transaction type though to cut down on the number of empty fields for
each transaction type, and also to limit the number of options that is
allowed in each field. For example across the transaction types
credit, capture, authorize, force, charge, and voice authorize the
authorization_source_code field might have 8 possible values, but in
any one transaction type it probably only has 3 at the very most.

Would most of you create a separate tabel for each transaction type?

I'm also curious how others would handle the batch closings. In the
past I have created separate tables for open transactions and
transactions that have been captured/settled. When a transaction is
captured it's moved to a different table instead of just having a
column to mark it as captured. Normally I would select all the
transactions to capture, insert them into the captured table, delete
them from the open transactions table, process the batch, and if the
batch goes through commit everything. That narrows down the number of
things that can go wrong after you have submitted the batch. The
alternative would be to just have a column to mark transactions as
capture and leave them all in one table. I've always been paranoid
about doing that because it leaves open the possibility of capturing
thousands of transactions twice if you have a bug, as opposed to a few
hundred at most.

I spent quite a few years working at payment gateways and am now
creating an open source platform that does the same thing that your
normal payment gateway does. It's been a while since I've had the
chance to look at this problem in a fresh light. Most of the
processing code at payment gateways is left pretty much untouched once
it's working, it's not something you go in and refactor every few
months even if it's not perfect.

Would appreciate any feedback.

Chris

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Didier Gasser-Morlay 2007-02-24 09:57:29 Re: schema design question
Previous Message Magnus Hagander 2007-02-24 07:17:28 Re: 5 Weeks till feature freeze or (do you know where your patch is?)