Clarify a db relationship problem

[beacon]

Hi everybody,

I'm having trouble normalizing or setting up relationships for my tables. Here's what I've got so far:

tblPatients:
PatientPrimary# (Number) [Pk]
PatientSecondary# (Text)

tblPatientInfo:
PatientInfoID (Auto) [Pk]
PatientEpisode (Number)
PatientPrimary# (Number) [Fk]

tblChartInfo:
ChartID (Auto) [Pk]
PatientEpisode [Fk]
ChartItem (Text)

- My relationship between tblPatients and tblPatientInfo is PatientPrimary# -> PatientPrimary#

- My relationship between tblPatientInfo and tblChartInfo is PatientInfoID -> PatientEpisode

Each patient can only have on episode number, but there may be multiple items for ChartInfo per episode. If Patient #100 is on episode 1, they may have 10 chart items for that episode. As of right now, if I enter more than one of the same episode for a patient on the tblPatientInfo, the table will accept it, but it shouldn't.

If I change the primary key of the tblPatientInfo to episode number, then when I enter a value on the tblChartInfo, I can't have multiple items for one episode without have an index/duplicate errors message appear.

What am I doing wrong? I've attached two sample databases...one to show the different primary keys for the tblPatientInfo.

* Just for the record, I posted a similar, although different question on a different forum, but haven't yet gotten a response, so please don't chastise me if you also read that post.

Thanks,
beacon

[mshmyob]

Hello Beacon,

Could you describe what the database is meant to do so I can get an understanding of what you want done.

Your 2 samples don't have a table 'tblChartInfo' although you mention it. You are also mixing up terminology so I am having trouble figuring it out.

To determine relationship types (ie: 1:1, 1:M, or M:N) you need a minimum of 2 business rules per linked set of tables (one rule for each direction of the link - this will determine your relationship type). Once you know the relationship types the design is very easy.

Therefore give me an explanation of your database is used for and then what you want to accomplish and I will help you work it out.

cheers,

[NeoPa]

@beacon
As you've given us the heads-up we'll just treat them as different questions (as you say).

FYI: Should you need a question to be cross-forum, you can post a linking thread in another forum which won't contain any discussion, simply a link to the original thread. That way all discussion is kept together.

[NeoPa]

I have two problems understanding your explanation :

1. @beacon
   Is it possible that multiple tblPatientInfo records are allowed but they must all share the same PatientEpisode value?
2. @beacon
   This seems strange, particularly bearing in mind you have a tblChartInfo.PatientEpisode field.

@beacon
This sounds like you have specified the index on tblChartInfo.PatientEpisode as unique. This would not be correct.

[beacon]

@mshmyob
People at my work will be entering deficiencies on forms for patients for each discharge from our hospital. Each patient will have a primary number used as an identifier that won't change. For each patient they will have episodes that identify their various admissions to the hospital. The episode will also have a discharge date, date received, audited date, program, unit, and auditor that won't change for that one episode. Then, there may be many forms per episode that have deficiencies (missing signature, missing date, etc.). Because the patient's episode is determined by another system, the user needs to be able to enter the episode, but not be able to use more than one per patient, but be able to use many per form.

So patient1 -> episode1 -> form1, form2, form3, form4
patient1 -> episode2 -> form5, form6, form7
patient2 -> episode1 -> form8, form9, form10, form11, form12
is okay and what I'm looking for.

This is what is happening on db3
patient1 -> episode1 -> form1, form2, form3, form4
patient1 -> episode2 -> form5, form6, form7
patient2 -> episode1 -> form8, form9, form10, form11, form12
** patient1 -> episode1 (this is a mistake) -> form13, form14, form15

The problem I'm having on attachment db2 is that patient 2 can't have an episode 1 because episode is the primary key for that table and won't allow duplicate values on the tblDeficiency.

On db3, it will allow multiple episodes on tblDeficiency, but will also allow duplicates on tblPatientInfo. The user needs to be able to enter the patient episode, but once it's entered it can't repeat for that patient. It needs to be able to be re-used by other patients.

Your 2 samples don't have a table 'tblChartInfo' although you mention it. You are also mixing up terminology so I am having trouble figuring it out.

Sorry about that....the table is really called tblDeficiency, but I wasn't sure that anyone looking at the table would be able to tell what the table was for. I should have specified that I called it something different in my description, but I forgot that I had changed it. For the record, tblDeficiency will be the 3rd table.

To determine relationship types (ie: 1:1, 1:M, or M:N) you need a minimum of 2 business rules per linked set of tables (one rule for each direction of the link - this will determine your relationship type). Once you know the relationship types the design is very easy.

I thought I had it going with db3 until I realized that I could re-enter the same episode for one patient. I hope this helps clear things up a bit as what I'm doing with this is a lot different than what I'm used to doing.

Ultimately, on a form, I would like to search for a patient and episode, if the episode exists, display error and try again. If it doesn't exist, the user can add it. On the form they will enter the discharge date and other stuff on the tblPatientInfo. Then on a second tab of a tab control, have a subform that allows the user to enter multiple forms and the mistakes made on them.

Therefore give me an explanation of your database is used for and then what you want to accomplish and I will help you work it out.

Let me know if I need to go into more detail and thanks a ton for helping me out. I've been trying to get just the tables set up all week. Once I get those setup the rest should be fairly easy to implement, but I want to ensure that I don't have to make any changes to the tables once they are set.

Anyway, thanks again!

cheers,[/quote]

[beacon]

@NeoPa
Hi NeoPa,

For one patient the episode number can't be repeated. The episode number maybe repeated for other patients. Episode is equivalent to admission number.

If patient1 is on episode1, then patient1 can have multiple deficiencies (or problems) with his/her chart. Once patient1 has been discharged, episode1 can't be repeated for that patient.

However, users need to be able to type the episode number in...it can't be autonumber because the episode numbers are already associated to the patients in another system.

So one patient will have the following:
- Primary ID,
Secondary ID
For each primary ID there will be
- Episode # (Unique for each patient, but needs to not be autonumber),
Discharge Date,
Date Received,
Date Audited,
Program,
Unit,
Auditor
For each episode number, there needs to be
- Ability to enter multiple forms
Each form will have
- Deficiency,
Date,
Quantity,
Employee ID,
Employee Name,
Employee Department

Does this, along with my previous post help make things a little clearer? I think I've racked my brown on this so much that even if the solution was right in front of my face I wouldn't see it.

Any help is very much appreciated.

- beacon

[mshmyob]

Ok based on what you are saying I have made some assumptions. Please correct me if I am wrong.

Each PATIENT can have many EPISODES.
Each EPISODE can have many PATIENTS.

Each EPISODE can have many DEFICIENCIES
Each DEFICIENCY can have many EPISODES

Therefore you need 5 tables

PATIENT ---> PATIENTDETAIL ---> EPISODE ---> EPISODEDETAIL ---> DEFICIENCY

This will allow a paitient to have many episodes but allow different patients to have the same episode number. It will allow each patient episode to have many deficiencies but only one of the same deficiency for that episode per patient.

Primary keys are as follows:
PATIENT - PK is PatientID
EPISODE - PK is EPISODEID
DEFICIENCY - PK is DEFICIENCYID
PATIENTDETAIL - PK is PATIENTID and EPISODEID
EPISODEDETAIL - PK is EPISODEID and DEFICIENCYID and PATIENTID

All PK's can be typed in if you wish - PK's do not have to be autonumbers.

If you have any other questions or my thinking is wrong please let me know.

cheers,

[beacon]

Hi Mshmyob,

I think we're almost on the same page. I think it's my lack of understanding that's separating us.

A PATIENT can have many EPISODES, but an EPISODE can't be repeated for any one PATIENT. Will setting up my tables like you described keep this from happening?

For each EPISODE, there can be unlimited DEFICIENCIES. Think of it this way...each patient has a chart with forms in it. Each time the patient discharges, all the forms are taken out and checked. When they are checked there may be hundreds of mistakes (deficiencies), but the mistakes are unique to just that episode (the same forms are used all the time). Once the patient is readmitted (and subsequently starts a new episode), the deficiencies (or mistakes on forms) start over again but for the current episode.

Based on what you wrote, would the only necessary thing in the PATIENTDETAIL and EPISODEDETAIL tables be the primary keys? All my info (like discharge date or unit on the EPISODE table) don't need to be on the PATIENTDETAIL or EPISODEDETAIL tables, right?

Thanks...beacon

[mshmyob]

Hello Beacon,

I think I understand what you are saying. The design I gave you should do what you want. I will explain in more detail what is stored in the tables.

tblPATIENT - Pertinent patient info such as name, address, etc. - each patient appears once in this table.

tblDEFICIENCY - Pertinent info describing a deficiency - each dificiency only appears once in this table

tblEPISODE - Pertinent info for an episode

tblPATIENTDETAIL - PatientID and EpisodeID so that a patient can be checkin many times to the hospital at different times. You can store checkin/checkout dates here or the EPISODE table.

tblEPISODEDETAIL - DeficiencyID, EpisodeID, PatientID as a composite PK will allow you to generate many forms for a patient based on a single Episode. Many patient can have the same Episode but different Deficiencies therefore different forms. When the same patient is admitted a different day he/she will have a new Episode and you assign new or the same deficiencies for that Episode.

Does that sound right to you?

If you need further clarification I could throw together a simple sample database for you to look at based on the data you uploaded.

cheers,

@beacon

[mshmyob]

Here is a sample database. I created the tables and threw in some made up data. It will open a report for you when you start it that will show different episodes with different patients per episode with different deficiencies.

Grouped on Episodes/ sorted by PatientID.

See if this is what you mean.

cheers

[beacon]

Hi Mshmyob,

I played around with what you suggested and I'm attaching the database. When I try to expand the subdatasheets for the PATIENT table (or tblPatient), it's not cascading the info like I assumed and it's showing the wrong info.

The EPISODES aren't repeating for the one patient (good thing), but the same info is showing up under each patient (bad thing).

The same DEFICIENCIES are also repeating for each patient (bad thing). When I enter the episode or deficiencies for one patient, it's like it copies it to the other patients.

It should cascade like this:
Patient #1
-> Episode #1
-> Deficiency #1
Deficiency #2
Deficiency #3 (All of these can be repeated for this patient)
-> Episode #2
-> Deficiency #4
Deficiency #5

Patient #2
-> Episode #6
-> Deficiency #6
Deficiency #7
Deficiency #8
Deficiency #9

If I remove the EPISODEDETAIL table and link the EPISODE table directly to the DEFICIENCY table, would that allow me to have multiple deficiencies for that one episode?

[mshmyob]

Did you download my previous example?

[beacon]

I did download it. I was in the middle of trying to post something back while you posted your sample and our paths crossed.

The same problem I'm having with the sample I attached is there with the one you attached too. The information for EPISODE #1 (you have Episode Date on yours) is the same for every patient that has episode one. This information will be different for each patient.

The way it is setup, it looks like all the patients were admitted on the same day for their first episode.

It looks like it's close, but the information for each episode needs to be unique for each patient and needs to be able to be repeated for a given episode. For instance, a patient can't have two #1 episodes, but can have similar deficiencies for an episode. After the episode, things need to be able to repeat because there are multiple possibilities for deficiencies (different employees, dates, quantities, etc.)

[mshmyob]

Since I did not know what went into episode I just threw a date in their. If you want to know when each person got admitted you would put an admited date in the tblPatientDetail table for that episode. Put any information that is unique for a patient for a specific episode in the tblPatientdetail table. Any information that is not unique for an episode but is common between many patients you would put in the tblEpisode.

Does that make sense to you.

cheers,

@beacon

[beacon]

It does make sense...let me play around with it some more and see if things turn out the way I hope.

Thank you for putting up with me mshmyob...