January 20, 2009 at 2:37 pm
We have an interesting dilemna that I need some help in redefining.
We have a table named ptDemographics where we store data about the patients in our EMR. We also have a table named ptContacts that stores data about contacts for the patient, being Subscribers for insurance plans, emergency contacts, or just plain secondary contact info. These two tables are linked by the Medical Record Number or MRN.
Now, when we go to select a subscriber contact for the Patient's insurance, a new ptContacts record is created, however, the Patient themselves may also be selected as the subscriber but I do not want to create a new ptContacts record since this person already has a ptDemographics record. However, because of the current design, a new ptContacts record will be created and when they go to change subscribers, BOTH the ptdemographics record and the ptContacts record are shown in the Select dialog. We show both because we must show ptDemographics records in case they do not have a ptContacts record.
Now, if the Patient's spouse is selected as Subscriber a new ptContact record will be created for the spouse. But now if the Spouse then becomes a Patient, they will also have a record in both ptDemographics and ptContacts and we're trying to get away from all that duplication.
I'm sure many of you have done something like this before and am having trouble defining how to perform this type of thing.
January 20, 2009 at 2:41 pm
The base-problem is the database design. You shouldn't have contact data in the patients table. It should all be in one place, with a foreign key to connect the two up. Then you wouldn't have these problems at all.
Since it sounds like it's too late to do that, you'll need to do something like use a Union operator to eliminate duplicates from the select, and you'll need to make the insert code smart enough to not insert into contacts if there's a patient with the same data, I guess.
What happens if both spouses are patients, and both are contacts for each other? Don't you then (with the current design) NEED both people in both places?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 20, 2009 at 2:46 pm
GSquared (1/20/2009)
The base-problem is the database design. You shouldn't have contact data in the patients table. It should all be in one place, with a foreign key to connect the two up. Then you wouldn't have these problems at all.
Hey, don't get me talking about the DB design, this was done by amateurs that knew nothing but Access before this package was built. I'd love to do this but it may be a major rewrite of two applications to do this, I haven't dug that far, yet.
GSquared (1/20/2009)
Since it sounds like it's too late to do that, you'll need to do something like use a Union operator to eliminate duplicates from the select, and you'll need to make the insert code smart enough to not insert into contacts if there's a patient with the same data, I guess.
I am not familiar with using Unions to do anything and not sure how they work.
Besides, there is different data stored in each table, we will definitely need to "merge" the tables.
GSquared (1/20/2009)
What happens if both spouses are patients, and both are contacts for each other? Don't you then (with the current design) NEED both people in both places?
It is already like that, yes they would be in both places if both were patients and contacts for each other.
January 20, 2009 at 3:01 pm
Union works like this:
select x,y,z
from dbo.Table1
UNION
select a,b,c
from dbo.Table2
The details are in Books Online, but, simply put, it puts two data-sets on top of each other, and gets rid of duplicates. Union All does the same, but doesn't get rid of the duplicates.
I'm not actually sure you'll be able to make that work, since it doesn't leave you with a unique row identifier that would allow you to update/delete the data, since it won't know which table to update/delete from, and if you add such in, it will automatically make it a non-duplicate, which means Union will still display both copies.
What you may have to do is include a Left Outer Join from one table to the other, on the columns you don't want duplicates for, and eliminate the ones that are in both. Are you familiar with how to do that?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 20, 2009 at 3:51 pm
Well, ptDemographics doesn't have the same structure as ptContacts but they both have an MRN field that "point" to each other. We currenlty have a TableAdapter that is filled with the result set from both ptDemographics and ptContacts but places 'Demographics' in the "Type" column and a blank in the "PK" column for ptDemographics records and 'Contacts' in the "Type" column and the MRN in the "MRN" column for ptContacts. That is how we essentially differentiate between the two.
I am curious as to how a union would remove the duplicates.
Here is the structure for ptDemographics:
CREATE TABLE ptDemographics (
[MRN] [varchar](25) NOT NULL,
[FName] [nvarchar](35) NOT NULL,
[MI] [nvarchar](25) NULL,
[LName] [nvarchar](35) NOT NULL,
[Surname] [nvarchar](25) NULL,
[Address1] [char](50) NULL,
[Address2] [char](50) NULL,
[City] [char](25) NULL,
[State] [char](2) NULL,
[Zip] [char](10) NULL,
[HomePhone] [nvarchar](25) NULL,
[WorkPhone] [nvarchar](50) NULL,
[DateOfBirth] [datetime] NULL,
[SocialSecurityNum] [nvarchar](15) NULL,
[Gender] [nvarchar](6) NULL,
[PrimaryIns] [char](35) NULL,
[Comment_1] [nvarchar](250) NULL,
[Comment_2] [nvarchar](250) NULL,
[FamilyHistory] [char](250) NULL,
[SocialHistory] [char](250) NULL,
[PrimaryCareDx] [char](35) NULL,
[MRN2] [nvarchar](25) NULL,
[UserID] [char](10) NULL,
[Deleted] [int] NULL,
[DeletedBy] [char](10) NULL,
[DeletedDate] [datetime] NULL,
[Physician] [nvarchar](50) NULL,
[id] [int] NULL,
[BillType] [char](10) NULL,
[Race] [nvarchar](20) NULL,
[SupportName] [nvarchar](50) NULL,
[SupportPhone] [nvarchar](25) NULL,
[Relationship] [nvarchar](25) NULL,
[CreateDtTm] [datetime] NULL,
[UpdateDtTm] [datetime] NULL,
[CellPhone] [nvarchar](25) NULL,
[EmergencyName] [nvarchar](50) NULL,
[EmergencyPhone] [nvarchar](25) NULL,
[EmergencyRelationship] [nvarchar](25) NULL,
[OtherPhone] [nvarchar](25) NULL,
[Email] [nvarchar](255) NULL,
[Religion] [nvarchar](255) NULL,
[Nickname] [nvarchar](50) NULL,
[Suffix] [nvarchar](25) NULL,
[Title] [nvarchar](25) NULL,
[MaritalStatus] [nvarchar](25) NULL,
[Employer] [nvarchar](25) NULL,
[DateOfDeath] [datetime] NULL,
[Country] [nvarchar](50) NULL,
[EmploymentStatus] [nvarchar](25) NULL,
[StudentStatus] [nvarchar](25) NULL,
[PhonePref] [int] NULL,
[EmergencyContact1] [int] NULL,
[EmergencyContact2] [int] NULL,
[SecAddress1] [nvarchar](50) NULL,
[SecAddress2] [nvarchar](50) NULL,
[SecCity] [nvarchar](25) NULL,
[SecState] [nvarchar](2) NULL,
[SecZip] [nvarchar](10) NULL,
[SecEmailAddress] [nvarchar](255) NULL,
[SecCountry] [nvarchar](50) NULL,
[SuppressPatientStatement] [bit] NULL
) ON [PRIMARY]
and here is the structure for ptContacts:
CREATE TABLE ptContacts (
[ContactID] [int] IDENTITY(1,1) NOT NULL,
[MRN] [varchar](25) NULL,
[ContactName] [nvarchar](50) NULL,
[FName] [nvarchar](50) NULL,
[MI] [nvarchar](50) NULL,
[LName] [nvarchar](50) NULL,
[Address1] [nvarchar](50) NULL,
[Address2] [nvarchar](50) NULL,
[City] [nvarchar](50) NULL,
[State] [nvarchar](2) NULL,
[Zip] [nvarchar](15) NULL,
[HomePhone] [nvarchar](25) NULL,
[WorkPhone] [nvarchar](25) NULL,
[MobilePhone] [nvarchar](25) NULL,
[Fax1] [nvarchar](25) NULL,
[Fax2] [nvarchar](25) NULL,
[Fax3] [nvarchar](25) NULL,
[Email1] [nvarchar](100) NULL,
[Email2] [nvarchar](100) NULL,
[Email3] [nvarchar](100) NULL,
[Website] [nvarchar](100) NULL,
[DOB] [datetime] NULL,
[Gender] [char](1) NULL,
[SSN] [nvarchar](50) NULL,
[AlternateID] [nvarchar](50) NULL,
[AlternateIDType] [nvarchar](50) NULL,
[ContactType] [int] NULL,
[MaritalStatus] [nvarchar](25) NULL,
[EmploymentStatus] [nvarchar](25) NULL,
[Employer] [nvarchar](100) NULL,
[UpdatedByID] [int] NULL,
[UpdateDtTm] [datetime] NULL,
[DeletedByID] [int] NULL,
[DeletedDtTm] [datetime] NULL,
[PhonePref] [int] NULL,
[OtherPhone] [nvarchar](25) NULL,
[Suffix] [nvarchar](25) NULL,
[Title] [nvarchar](25) NULL,
[NickName] [nvarchar](25) NULL,
[Country] [nvarchar](25) NULL,
[Relationship] [nvarchar](25) NULL,
[StudentStatus] [nvarchar](25) NULL,
[School] [nvarchar](25) NULL
) ON [PRIMARY]
January 20, 2009 at 3:52 pm
Another sticky issue we have is that when a person is, say, a subscriber contact AND and emergency contact, they will have two records in ptContacts.
Whoa....
January 21, 2009 at 8:34 am
Any more thoughts on the union with the structures I've provided?
January 21, 2009 at 8:42 am
It would look something like this:
;with
Demos
(Source, ContactID, MRN, FName, MI, LName, Address1, Address2,
City, State, Zip, HomePhone, WorkPhone, CellPhone)
as
(select 'Demographics', null, MRN, FName, MI, LName, Address1, Address2,
City, State, Zip, HomePhone, WorkPhone, CellPhone
from dbo.ptDemographics
left outer join dbo.ptContacts
on ptDemographics.MRN = ptContacts.MRN
where ContactID is null),
Contacts
(Source, MRN, FName, MI, LName, Address1, Address2,
City, State, Zip, HomePhone, WorkPhone, CellPhone)
as
(select 'Contacts', ContactID, MRN, FName, MI, LName, Address1, Address2,
City, State, Zip, HomePhone, WorkPhone, MobilePhone
from dbo.ptContacts)
select *
from Demos
union all
select *
from Contacts
Since I don't know the exact columns you need in the output, nor what to put in the Where clause for each CTE, this is just an outline. But it should give you at least approximately what you need.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 21, 2009 at 10:05 am
Let me post the actual SQL being used to fill that selection grid.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply