If it's not too much to ask might I solicit comments/suggestions on my table
design? There are really two sections. The top is the strict accounting
aspect, the bottom is for invoicing and other functional requirements and is
intimately linked to the accounting aspect. The accounting part can stand
alone, the invoicing part cannot, but depends on the accounting portion.

***The first two tables define accounts and transactions***

LEDGERACCOUNT
--------
LEDGERACCOUNTID INTEGER (PK)
LEDGERACCOUNTTYPE - Credit or Debit
DESCRIPTION VARCHAR(30)

(Notes: LEDGERACCOUNT stores information for accounts including: CASH,
PATIENT A/R, INSURANCE A/R, DISCOUNT, WRITE OFF, REVENUE)


TRANSACTIONTYPE
---------
TRANSACTIONTYPEID INTEGER (PK)
DESCRIPTION VARCHAR(30)
DEBITACCOUNTID INTEGER (FK)
CREDITACCOUNTID INTEGER (FK)

(Notes: TRANSACTIONTYPE defines different types of transactions. For example
for a "CHARGE" transaction type DEBITACCOUNTID will point to the PATIENTAR
account and CREDITACCOUNTID will point to to the REVENUE account. For a
"PATIENTPAYMENT" transaction type DEBITACCOUNTID will point to the CASH
account and CREDITACCOUNTID will point to the PATIENTAR account.)


***The next three tables define actual financial transactions, stored in a
hierarchy. TRANSACTIONSET is a batch of one or more financial transactions,
stored in JOURNAL. JOURNAL defines a specific financial transaction
(CHARGE, PAYMENT, REFUND, et. al.) detailed as two entries in LEDGER - one
credit and one debit***

TRANSACTIONSET
----------
TRANSACTIONSETID INTEGER (PK)
PATIENTID INTEGER (FK)
OFFICEID INTEGER (FK)
TRANSACTIONSETDATETIME DATETIMESTAMP
CREATED DATETIMESTAMP
CREATEDBY INTEGER

(Notes: referred to as a "BATCH" in other accounting systems. A set of one
or more journal entries posted at the same time)

JOURNAL
----------
JOURNALID INTEGER (PK)
TRANSACTIONSETID INTEGER (FK)
TRANSACTIONTYPEID INTEGER (FK)
NOTES BLOB

(Notes: each entry here will correspond to a single transaction, i.e. a
"CHARGE", "PAYMENT", "REFUND", etc. For each entry here there will be two
entries in the LEDGER table, no more, no less. One CREDIT and one DEBIT.)

LEDGER
--------
LEDGERID INTEGER (PK)
JOURNALID INTEGER (FK)
ACCOUNTID INTEGER (FK)
AMOUNT DECIMAL(18,2)


***OK, that is accounting system. There are other functional requirements
of my system, that don't mesh perfectly with the accounting side. The
following tables address these other requirements.***


SUPERBILL
----------
SUPERBILLID INTEGER (PK)
SUPERBILLDATE DATE
DOCTORID INTEGER (FK)
PRIMARYINSURANCEID INTEGER (FK)
SECONDARYINSURANCEID INTEGER (FK)
TERTIARYINSURANCEID INTEGER (FK)
NARRATIVE VARCHAR(60)
REFERALDOCTORID INTEGER (FK)
APPOINTMENTID INTEGER (FK)
FILESURGERYDATE True/False domain
SURGERYDATE DATE
PRIORAUTHORIZATION VARCHAR(60)
NEEDSELECTRONIC True/False domain
NEEDSSECONDARY True/False domain
PLACEOFSERVICE INTEGER (FK)
WALKOUTSTATEMENTNOTES VARCHAR(60)
NOTES BLOB

(Notes: for SUPERBILL, think "INVOICE". A superbill will be related to one
or more TRANSACTIONSET records via the detail records below.)

SUPERBILLITEM
------------
SUPERBILLITEMID INTEGER (PK)
SUPERBILLID INTEGER (FK)
JOURNALID INTEGER (FK)
FILEPRIMARY True/False domain
FILESECONDARY True/False domain
FILETERTIARY True/False domain
DIAGNOSISCODE VARCHAR(10)
PROCEDURECODE VARCHAR(10)
MOD1 VARCHAR(2)
MOD2 VARCHAR(2)
MOD3 VARCHAR(2)
MOD4 VARCHAR(2)
TYPEOFSERVICE VARCHAR(2)
UNITS INTEGER
EMPLOYEEID INTEGER (FK)

(Notes: each entry here is a CHARGE and this contains information necessary
to file insurance. There is a 1 to 1 relationship with JOURNAL)

SUPERBILLACTIVITY
---------------------
SUPERBILLACTIVITYID INTEGER (PK)
SUPERBILLID INTEGER (FK)
SUPERBILLITEMID INTEGER (FK)
JOURNALID INTEGER (FK)

(Notes: each entry here is something associated with a charge - PAYMENT,
REFUND, CREDITAPPLIED, et. al. Again there is a 1 to 1 relationship with
JOURNAL as well as a 1 to many with SUPERBILLITEM. That is one
SUPERBILLITEM to many SUPERBILLACTIVITY. The purpose is to link activity,
such as WRITEOFF, REVERSE, REFUND, PAYMENT, et. al. with a specific charge.)