I've been using this paradigm for years: Family Table (holds address, home phones, and primary addressee link)
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) 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. 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. 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? On Wed, May 30, 2012 at 8:20 AM, Ben Petersen <[email protected]>wrote: > Hi Bill, > > It would seem (but I'm not too clear at this point) that your bill-to > link should be limited to one place and, as best I understand things, > it would seem the transaction detail would be it if a single patient > visit might entail billing multiple parties. > > Maybe you end up with something like: > > Family > Patient > PatientVisits > PatientVisitsBillableItems > > Where Family contains data shared by several people, or just a single > patient > PatientVisits is the header to PatientVisitsBillableItems. > > There's nothing intelligent intended here <g>, just throwing out ideas > to help define things. > > Ben > > > > On Wed, May 30, 2012 at 6:21 AM, William Stacy > <[email protected]> wrote: > > Gil and Ben, thanks for the thoughts. My family table has become mostly a > > physical location, and my "fam" table has become mostly that: a small > table > > that contains the postal address lines, a home phone number (which > probably > > needs to be in its own communication table), a primary addressee link > (which > > can and does change through death, divorce, etc.). Indeed, my fam table > > should probably just be the physical location, which pretty much cannot > > change (ok there's eminent domain wipeout), while the people living there > > can be moved in and out as needed. > > > > My person table ("pat") now contains a "bill-to" link which can change > over > > time, and my transaction header table also contains "bill-to" links which > > cannot be changed except through error correction. This bill-to link > refers > > to the primary account holder, who pays the patient share of any > > obligation. It appears that third party payors and agreed discounting > needs > > to be handled at the line item detail table level, or else in a separate > > detail of details table. I'm thinking of scrapping the latter idea in > favor > > of using additional detail lines to "transfer" parts of line items to > > various 3rd party accounts. > > > > Like the transaction header table, the detail table needs its own > "bill-to" > > link for each item that gets charged to a 3rd party, and I'm working > toward > > that goal right now. Anyone have any problem with this approach? > > > > Again, thanks in advance. Fresh input is helpful in this challenging > task. > > > > > > Bill > > > > > > > > On Tue, May 29, 2012 at 11:08 PM, Ben Petersen <[email protected]> > > wrote: > >> > >> Hi Bill, > >> > >> I have an app that could translate to family and individuals. In that > >> case the bill is generated to the family record, but the individuals > >> are only represented as line items and are not referred to by their > >> ID# other than as text in the transaction description. So, as they > >> move around the billing history stays with the family. > >> > >> If, on the other hand, when you look back you are wanting to extract a > >> history for an individual that won't work elegantly. To do that, the > >> bill would be issued to the family record (in the header of the > >> invoice) with individual IDs included in the transaction detail table. > >> > >> Each individual record would have both their individual id which would > >> never change, and a family ID that could and links to a family table. > >> You could then reference old bills and all the line items would > >> remain, and query all transactions for a particular individual ID > >> regardless of how many families they have belonged to in the past. > >> > >> Ben > >> > >> > >> > >> > >> On Tue, May 29, 2012 at 4:44 PM, William Stacy > >> <[email protected]> wrote: > >> > I'm restructuring my billing system to become more flexible and > correct > >> > and > >> > would appreciate any suggestions/pointers from those more skilled than > >> > I. > >> > My restructure is mostly due to two things. One, I've always billed > by > >> > family accounts, where one family member is assigned as the account > >> > holder. > >> > The problem arises now and then that a family member leaves the nest > and > >> > either then has their own account, or joins another family which has > its > >> > own > >> > account. > >> > > >> > The problem is with the old transactions of prior family members not > >> > showing > >> > up in account lookbacks. > >> > > >> > I know one popular way is to have everyone have their own accounts and > >> > just > >> > split family payments into their individual parts. It's not pretty, > and > >> > requires more computing and more complex statements, but it would > solve > >> > the > >> > problem. > >> > > >> > I've also been toying with handling it at the transaction line item > >> > level, > >> > assigning a permanent bill-to person/entity at that level which can > also > >> > be > >> > used to handle 3rd party issues. Which is the other part of this > >> > restructure. > >> > > >> > TIA > >> > > >> > Bill > >> > > >> > > >> > > >> > -- > >> > William Stacy, O.D. > >> > > >> > Please visit my website by clicking on : > >> > > >> > http://www.folsomeye.net > >> > > >> > > >> > > >> > >> > > > > > > > > -- > > William Stacy, O.D. > > > > Please visit my website by clicking on : > > > > http://www.folsomeye.net > > > > > > > > > -- William Stacy, O.D. Please visit my website by clicking on : http://www.folsomeye.net
