child with multiple parents 1

[ghacig]

Dear all,

I am creating a database for a medical practice. There are three locations from which a medical test can be ordered: Hospital, Office and Phone conversation. Each of those locations represents an encounter with the patient and has a table:
Hospital: VEEG
VEEGID (Autonumber) primary key
MRN (Text) foreign key (linked to demographics)

Clinic: ClinicVisit
ClinicID (Autonumber) primary key
MRN (Text) foreign key (Linked to demographics)

Phone conversation: Phone
PhoneID (Autonumber) primary key
MRN (Text) foreign key (to demographic)


There is a table called Tests. Tests can be ordered from either three locations. I would like to be able to track where Tests were ordered and link them to the specific patient encounter. I was wondering if this works: I will create three foreign keys in Table Tests as such:

Table: Tests
TestID (Autonumber) primary key
VEEGID (longinteger) foreign key linked to VEEG
ClinicID (Longinteger) foreign key linked to Clinic
PhoneID (LongIntenteger) foreign key linked to Phone

I will create the forms such as you can only enter one of the three foreign keys for any given record.

My question: Will this work, or is it breaking some important relational rules that will get me in trouble down the road.

I appreciate any help. Thanks.

 

[RoyVidar]

Quite possible I'm misunderstanding the challenge, or you have perhaps simplified the real challenge. By the informatin given, I'm inclined to perceive "patient encounter" as a single "phenomena type", and deal with it through one entity type (one table), and have the encounter type be an attribute to that table:

[tt]tblPatientEncounter
PEID
MRN
EncounterType[/tt]

Where the encounter type stores whether it is by phone, visit or thru hospital.

This would provide your tblTest to have only one foreign key, and an easier setup.

Roy-Vidar

 

[ghacig]

Thanks for your response. Actually, patient encounter is just the concept. The actual Hospital admission, Clinic visit or phone conversation are quite different and are each made of several tables, related to the parent table of each(VEEG, ClinicVisit, or Phone) via the primary key of the parent table. As for now, there is not single table called PatientEncounter.

Is it OK to keep the Tests table with three foreign keys?

Thanks

 

[RoyVidar]

I suspected it was more;-)

I don't like it, but perhaps I'm a bit "puristic". I'd suggest to do a little search, perhaps in this forum, and the General Database forum (here's two threads relating to the topic of children with more than one parent thread669-940903, thread669-939257, and one on Nulls thread669-820770) or wait and see if others will share their view...

Roy-Vidar

 

[GingerR]

ghacig--i would definatly not set up the table in the way you suggested. What if later you can also order tests from (hypothetically) On Line? You'd have to go thru the entire db adding this item to all tables, queries, reports, forms. Yikes.

I would make:

Table: Tests
TestID (Autonumber) primary key
OrderedFromID (Long Integer-will be the hopital, clinic or phoneID)

Then figure out how to get the data into the table in a user-friendly way. I'm not sure how the rest of your data is all tied together, but in a simple way, if you have a form where someone is logging in that a test has been ordered, perhaps you'd have a drop-down box where they pick the origin of the test.

Have one combo box whose row source is a table which lists unique 'origins':

Table: TestOrigins
OriginID (Autonumber)
Origin (text; data: "Phone","Clinic","Hospital")

Once the user picks either Phone, Clinic, or Hospital, a second combo box's row source is altered to show the data from whichever of the three tables applies.

One thought: if the Hospital ID, Phone ID and Clinic ID are all autonumbers and could be the same number, then you'll have to have a second key field in your Tests table which designates 'ORIGIN' (OriginID).

Then in the future, when "On Line" is added to the mix, you simply add it to the TestOrigins table and the second combo box's row source/OnChange event. Everything else in the db will continue to work properly because you've set up the db properly to begin with.

Hope this helps....

g






Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[ghacig]

GingerR.

Thanks a lot. I believe this will work. You are right, the problem is that the primary key of all three tables(hospital, clinic and phone) is an autonumber and values will be repeated. Something like OriginID, or very simply, Origin (with options being: "Hospital", "Clinic", "Phone") should work.

Each of the three situations has its own form. I could add the Test form as a subform to each of the forms and set the default value for the Origin field either Hospital, Clinic Or phone, depending on the mother form.

Thanks a lot for the tip.

 

[willir]

As a follow up to GingerR solution.

You have a many-to-many relationship between tests and location origin of the tests / encounter.

A test / encounter can be ordered from many locations.
A location can order many tests.

Further to this, I found the terminolgy a tad confusing, and perhaps a more apt description would EncounterLocationID or EncounterTypeID

You then probably need to tie into this table some of the following...
PatientID
DateTestOrder
DateOfTest
InsurenceCompanyID (since a patient may invoke more than one insurence company - right?)

Richard

 

[ghacig]

Thanks Richard. The items you mentioned are already part of the Test table in addition to other fields. My question is whether I can link the EncoutnerTypeID field to the primary field of the Hospital, Clinic or Phone tables? Thanks.

 

[omniaccess]

I am creating a system with a similar issue to what you mentioned and I though I would add my two cents worth.

I have a system have used dates in many different areas, such as schedules, reminders, accounting, events, etc. My goal was to make a global history table that tracked when entries were added or edited in each area. I had first set up the table the way you had initially mentioned (one autonumber field, and then separate ID fields that link to other tables, making this table a child to many parent tables.

What others mentioned are exactly what I had to work though. If you add another Parent, you would need to add another ID link field. I later wanted to add Reports to the history, and realized I would have to keep adding new ID fields as I though up new areas to use this as a child table.

Here is what I did instead.

Autonumber - to keep them all separate
ID field - indexed but not set to unique - the ID from each parent
RelatedTo - field to identify WHAT TYPE of info it is
formSubmitted - What the subform name is that submitted the date.
DateCreated - capturing when the entry was added.
...more fields

I made a difference between what form submitted the info and what type of info it is because even though it was submitted though one form, there were several variations as to what the information was.

This way, when the entire list is being viewed, I could program a multitude of different forms that could be opened from the data based on its ID, what form submitted it, and what type of information it is.

One other point to make. If the child data will show on each of the parent forms, simply add a filter to only show information originally added by that subform. I have a filter button on several of the parent forms to say "show entries for this appointment" and "show all entries related to this client".

 

[ghacig]

Thanks Omniaccess, this is probably the solution I will adopt.