Create database tables

Obviously I did not have access to my client’s database on my system so I used the VMD file (CVISOutbound.vmd) they sent me to make a dummy From Database channel like this:

From there I clicked on the “Tables” tab:

And clicked on the Export Source Tables button which gave me the option to create the tables with this SQL code:

CREATE TABLE NHCCVISReport (MESSAGE_ID VARCHAR(50) NOT NULL  DEFAULT '',PARENT_ID VARCHAR(50) NOT NULL  DEFAULT '',MSHDateTimeofMessage DATETIME,MSHMessageControlID VARCHAR(50),PIDPatientIDExternalIDID VARCHAR(50),PIDPatientIDInternalIDID VARCHAR(50),PIDPatientIDInternalIDCheckDigit VARCHAR(50),PIDPatientNameFamilyName VARCHAR(50),PIDPatientNameGivenName VARCHAR(50),PIDDateTimeofBirth DATETIME,PIDSex VARCHAR(50),PIDPatientAccountNumberID VARCHAR(50),PVVisitNumberID VARCHAR(50),PVAdmitDateTime DATETIME,OBRFillerOrderNumberEntityIdentifier VARCHAR(50),OBRUniversalServiceIDIdentifier VARCHAR(50),OBRUniversalServiceIDText VARCHAR(50),OBRObservationDateTime DATETIME,OBRPlacerField VARCHAR(50),OBXSetIDOBX VARCHAR(50),OBXValueType VARCHAR(50),OBXObservationIdentifierIdentifier VARCHAR(50),OBXObservationValue VARCHAR(50),OBXObservResultStatus VARCHAR(50),OBXSetIDOBX1 VARCHAR(50),OBXValueType1 VARCHAR(50),OBXObservationIdentifierIdentifier1 VARCHAR(50),OBXObservationValue1 VARCHAR(50),OBXObservResultStatus1 VARCHAR(50),OBXSetIDOBX2 VARCHAR(50),OBXValueType2 VARCHAR(50),OBXObservationIdentifierIdentifier2 VARCHAR(50),OBXObservationIdentifierText2 VARCHAR(50),OBXObservationValue2 VARCHAR(50),OBXObservResultStatus2 VARCHAR(50),PRIMARY KEY (MESSAGE_ID,PARENT_ID)) ENGINE = InnoDB DEFAULT CHARSET='utf8';
CREATE TABLE NHCCVIS (MESSAGE_ID VARCHAR(50) NOT NULL  DEFAULT '',STATUS VARCHAR(255) DEFAULT 'W',PRIMARY KEY (MESSAGE_ID)) ENGINE = InnoDB DEFAULT CHARSET='utf8';

That gave me some basic empty tables to work with. After that I deleted this dummy channel.

Notice that the structure of tables for this interface is very flat. In the clients application they wrote stored procedures and VB.NET to populate the tables in question because at the time they wrote it was the easiest way. If this interface were being done from scratch it would probably make more sense to leverage the Translator to join together data from multiple smaller tables.

Just something to keep in mind when you are designing your own interfaces.

Next Step?

Now we have created the tables for the tutorial. The next step is to create the channel that we will use to read the database and transmit the HL7 messages.