Many to Many to Many Normalization

[Eric Cathell]

I had originally posted this on the SqlServer.Programming list..but you guys
seem to talk more about normalization here which is what I think my issue
is.
So I apologize if this is considered a crosspost.

This is a combination of 3 posts each are separated by -----------------
showing progressing detail in regard to my issue.

Tables:
Encounter
EncounterProvider
Provider
ProviderType

EnounterProvider is the junction table. Having EncounterID,ProviderID, and
ProviderTypeID.
ProviderType could really be named better. But it has to do with the role
the provider is playing at some point in that encounter.

So we have:
an encounter with multiple providers(each encounter can have 1 or more
providers) an encounter with a single provider as multiple
ProviderTypes.(each encounter can have 1 or more providerTypes using the
same ProviderID) an encounter with multiple providers as the same
ProviderType

I have tried to key all three IDs but even that causes issues. The data for
this comes from a Foreign system so we have no control on the data output
other than the interface between the two.
--------------------------------------------------
1 353 5 36

1 353 5 41

ignore 1 its a count field.

EncounterID=353

ProviderTypeID=5

ProviderID=36,41

I dont have an example of the alternate so Ill use some mocked data.

EncounterID=392

ProviderTypeID=4,5

ProviderID=41

here is a better example of what I am looking at:
| |
8138 1 217
8138 2 29
8138 4 42
8138 5 29
8138 5 29
8138 5 135
8138 5 41
8138 5 61
8138 5 425
8138 5 187
8138 5 184
| |
8138 is the encounter id

1,2,4,5 are the providerTypeIDs

the rest are the ProviderIDs

hopefully this is helpful....I can send a screenshot of the current
relationships if that will help too..


All of these values are in the EncounterProvider Table. As you can see there
can be multiple providerIDs: This relates to multiple doctors acting as the
same providerType(attending, Initial, ER)

There can also be multiple ProviderTypes for a single provider: this relates
to a single doctor acting as several providertypes(attending and Initial)

Hopefully that cleared things up a bit more.

All three fields do not guarantee a unique constraint. I need to know how to
fix this. Exploring some of the Access groups there is something about
breaking out another table with a 1:m relationship on the Junction table.
But I am not exactly sure which table. So I need some guidance.
-----------------------------------------------------------------------
Since its a junction table usually this would be done with 2 FK as a
composite primary key. However in this situation no two values create a
unique index. The same for 3 composite keys. Thus this table cannot have a
unique constraint in its current form. Therefore that leads me to believe
there is a normalization problem with my tables. My requirements are to
properly normalize this table.

 

[Jeff Boyce]

Eric

You have Encounters, Providers, and ProviderType (?ProviderRole).

And you have the combination of all three, in a junction table.

Why are you saying that there is not a unique combination of all three? As
soon as you have a duplicate of the three values, you are describing the
same encounter, with the same provider, fulfilling the same role/type. Why
show it more than once?

Have you tried opening the junction table in design mode and creating a
unique index on the three fields combined?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Immanuel Sibero]

Hi Eric,

After reading your requirements, looks like we need to expand on the table
definition using the Entity/Relationships data model. Conceptually, my
interpretation is that an encounter (i.e. visit) is defined as an occurence
of provider(s) getting together with a patient for the purpose of
performing a service. If that interpretation is correct then, I would want
to have the following tables for each ENTITY:
- tblProvider (ProviderID pk)
- tblService (ServiceID pk)
- tblPatient (PatientID pk)
So I'm proposing a new table called tblPatient and maybe changing the name
ProviderType to tblService

Based on your description of the business rules, I would need the following
junction tables for each RELATIONSHIP:
- tblProviderService (ProviderServiceID pk, ProviderID fk, ServiceID fk)
This table defines the many-to-many relationship between Provider and
Service
- tblEncounter (EncounterID pk, PatientID fk, ProviderServiceID fk)
This table defines the many-to-many relationship between ProviderService and
Patient

The above design would allow you to:
- have multiple providers providing one service in one encounter
- have one provider providing multiple services in one encounter
- have multipe providers providing multiple services in one encounter


HTH,
Immanuel Sibero

 

[Eric Cathell]

you are right...there was bad data coming over from the interface. A 3 key
Combined Key is now working... Thanks for the response...