My suggestion is have two different billing level both including the
family id and patient id - you then can query either the family or
patient - I do that in a law application where we bill either on a
client or matter level
j
On 5/30/2012 1:21 PM, William Stacy wrote:
On Wed, May 30, 2012 at 9:38 AM, Ben Petersen <[email protected]
<mailto:[email protected]>> wrote:
I've noticed that I'm getting back two copies of my post. Is that
happening to others?
> Family Table (holds address, home phones, and primary addressee
link)
Did I understand that there was a bill-to column here also?
no, although I think Jay is suggesting that. I do have a primary
adressee linking column for sending mail to families, but don't send
much postal mail any more. Usually its the same ID as the bill to
person, and often the same as the patient themself.
> Patient Table (holds name, personal phones, e-mail, date of
birth, family
> and "bill to" links)
> Transaction table (holds date, time, patient link, etc)
> Transaction detail table ( links to transaction table, holds
line item,
> price, quantity, patient share, and payments/credits)
My reflexive inclination here would be to not have the patient share
as a separate column, but a separate line item. Each line item with
it's own bill-to, using the patient id for their share. Eliminate
bill-to's from other tables.
Hmm. So by way of example, right now I have a trans. detail table
where a single line item and it's payment look like this:
(sorry if they don't line up) This forms the basis of patient
statements (parens indicate computed values):
Item# Desc Amt. Quan. (Amt*Quan) Pamt
10 Exam 150.00 1 (150.00) 10.00
1 Cash 10.00 1 ( -10.00) -10.00
The remaining $140 is handled with one or more more lines such as:
99 Disc. 60.00 1 (-60.00)
9 Ins Pmt 80.00 1 (-80.00)
and kept separate from the patient statement.
In your suggestion, it would look something like:
10 Exam 150.00 1 (150.00) Pt. Share: 10.00
1 Cash 10.00 1 (-10.00) Pt. Paid: -10.00
10 Exam VSP Share: 80.00
99 Agrmt
Disc: -70.00
9 Ins . Pmt 80.00 1 (-80.00) VSP paid: -80.00
(obviously each entry would need it's own posting date)
My statements are gonna look pretty long when you do this for every
line item (lens, frame, contact lens, coating, you name it). but it
does make sense to me.
> I'm moving toward changing this hierarchy, putting the Patient
table at the
> top, with the family table just being an incidental (and
temporary) location
> link.
Yep.
> I kind of like the idea of a person having several different
> addresses so maybe a separate linking table is actually needed
as Bill D.
> suggests.
It works well in the app I mentioned. Same for Phone/Fax
> One last thing. I've always had charges and credits in the same
column in
> the trans. detail table, with charges being positive currencies
and credits
> being negative, simple summing them establishing running
balances. Anyone
> object to that method, or should I separate out credits into
their own
> table?
Like so many things it depends, but if it haven't gotten in your way
so far, I don't think there is anything inherently wrong with it.
Ben
--
William Stacy, O.D.
Please visit my website by clicking on :
http://www.folsomeye.net
--
*J. Blaustein*
*J Blaustein Associates, Inc.*
* 12 Herrick Drive*
* Lawrence, NY 11559*
*516-371-3445 FAX 516-345-8009*
