On Wed, May 30, 2012 at 9:38 AM, Ben Petersen <[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
