Some obversations:

a) Is this for a single entity, or are there divisions, branches,
departments involved? May need additional fields & reporting for this.

b) Printed output - reports of patients accounts, statements,
reminders & mailing, aged listings, trial balances, reportwriter access

c) Cash accounting records not clearly detailed here - cash books,
monthly bank reconciliations

d) Budget figures required?

e) Ledger account entries typically have an opening balance, a debit
column, a credit column and a (current) balance column, with
a date for each posting to each account.

f) Are subaccounts required to provide a categorised breakdown
per account?

g) Manual Journal entry functionality is commonly required

h) Multiuser with access/role controls for security reasons

i) Ability to handle other accounting functionality - creditors
& suppliers accounts and payments, rent, repairs & maintenance,
vehicles, assets, equity & capital accounts etc

j) Year end accounting close off possibly months after the
actual end but still able to process & report on the new
year.

k) Reporting will be extensive - current output functions
will be a good guide to the level of complexity & transaction
volume, which will then indicate the type & extent of
storage & summarisation required.

HTH,
John

> -------Original Message-------
> From: Anthony Tanas <anthony@...>
> Subject: [firebird-support] My ACCOUNTING table structure
> Sent: 09 Dec '06 13:25
>
> 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.)
>
>