MySQL Script to Create Tables

 Top  Previous  Next
1.EDI_ClaimAttachments
 
CREATE TABLE `EDI_ClaimAttachments`(

   `ID` int(11) AUTO_INCREMENT NOT NULL,

   `HeaderID` bigint NOT NULL,

  /* LX */

   `DetailCounter` tinyint Not Null, /* LX01 */

  /* TRN */

   `TraceTypeCode` varchar(50) Null, /* TRN01 */

   `AttachmentControlNumber` varchar(50) Null, /* TRN02 */

  /* STC */

   `HealthCareClaimStatus01` varchar(50) NULL, /* STC01 */

   `HealthCareClaimStatus10` varchar(50) NULL, /* STC10 */

   `HealthCareClaimStatus11` varchar(50) NULL, /* STC11 */

  /* REF */

   `ReferenceIdentifierQualifier` varchar(2) NULL, /* REF01 */

   `ReferenceIdentification` varchar(50) NULL, /* REF02 */

  /* HI */

   `PrincipalDiagnosisCodeQual` varchar(3) NULL, /* HI01-01 */

   `PrincipalDiagnosisCode` varchar(10) NULL, /* HI01-02 */

   `DiagnosisQual1` varchar(3) NULL, /* HI02-01 */

   `Diagnosis1` varchar(10) NULL, /* HI02-02 */

   `DiagnosisQual2` varchar(3) NULL, /* HI03-01 */

   `Diagnosis2` varchar(10) NULL, /* HI03-02 */

   `DiagnosisQual3` varchar(3) NULL, /* HI04-01 */

   `Diagnosis3` varchar(10) NULL, /* HI04-02 */

   `DiagnosisQual4` varchar(3) NULL, /* HI05-01 */

   `Diagnosis4` varchar(10) NULL, /* HI05-02 */

  /*SVC*/

   `CompositeMedicalProcedureIdentifier` varchar(100) NULL, /* SVC01 */

   `ProcedureCodeQual` varchar(3) NULL, /* SVC01-01 */

   `ProcedureCode` varchar(10) NULL, /* SVC01-02 */

   `Modifier1` char(2) NULL, /* SVC01-03 */

   `Modifier2` char(2) NULL, /* SVC01-04 */

   `Modifier3` char(2) NULL, /* SVC01-05 */

   `Modifier4` char(2) NULL, /* SVC01-06 */

   `Modifier5` char(2) NULL, /* SVC01-09 */

   `Modifier6` char(2) NULL, /* SVC01-10 */

   `Modifier7` char(2) NULL, /* SVC01-11 */

   `Modifier8` char(2) NULL, /* SVC01-12 */

   `Amount` decimal(18, 5) NULL, /* SVC02 */

   `ProductOrServiceId` varchar(50) NULL, /* SVC04 */

  /* DTP */

   `SubmitDate` date Null, /* DTP03 */

  /* CAT */

   `AttachmentFormatCode` char(2) NULL, /* CAT02 */

  /*OOI */

  /* `AssociatedObjectType` varchar(20) NULL, */ /* omitted, OOI segment is static/fixed in this version*/

  /*BDS*/

   `Encoding` char(3) Not NULL, /* BDS01 */

   `PayloadLength` integer Not Null, /* BDS02 */

   `Payload` varchar(max) Not Null, /* BDS03 */

   `PayloadPath` varchar(100) NULL,

   `PayloadFileName` varchar(100) NULL,

   PRIMARY KEY (`ID`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 

2.EDI_ClaimAttachmentHeader
 
CREATE TABLE `EDI_ClaimAttachmentHeader`(

   `ID` int(11) AUTO_INCREMENT NOT NULL,

  /*File Level Data*/

   `Filename` varchar(100) NULL,

   `Version` varchar(12) NULL,

   `ImageFilePath` varchar(300) NULL,

   `ImageFilename` varchar(100) NULL,

   `TradingPartnerIDType` char(2) NULL,

   `TradingPartnerID` varchar(15) NULL,

   `ReceiveDate` datetime NULL,

   `TransactionType` char(2) NULL,

  /* ISA */

   `AuthInfoType` char(2) NULL, /* ISA01 */

   `AuthInfo` varchar(10) NULL, /* ISA01 */

   `SecurityInfoType` char(2) NULL, /* ISA03 */

   `SecurityInfo` varchar(10) NULL, /* ISA04 */

   `InterchangeSenderIDType` char(2) NULL, /* ISA05 */

   `InterchangeSenderID` varchar(15) NULL, /* ISA06 */

   `InterchangeReceiverIDType` char(2) NULL, /* ISA07 */

   `InterchangeReceiverID` varchar(15) NULL, /* ISA08 */

   `InterchangeDate` date NULL, /* ISA09 */

   `InterchangeTime` time NULL, /* ISA10 */

   `InterchangeControlNo` varchar(9) NULL, /* ISA13 */

   `AckRequested` char(1) NULL, /* ISA14 */

   `AppSenderCode` varchar(15) NULL, /* GS02 */

   `AppRecieverCode` varchar(15) NULL, /* GS03 */

   `GroupCreationDate` date NULL, /* GS04 */

   `GroupCreationTime` time NULL, /* GS05 */

   `GroupControlNo` varchar(9) NULL, /* GS06 */

   `TransactionSetControlNo` varchar(9) NULL, /* ST02 */

  /* BGN */

   `TransactionSetPurposeCode` varchar(50) NULL, /* BGN01 */

   `TransactionSetReferenceNumber` varchar(50) NULL, /* BGN02 */

   `TransactionSetCreationDate` varchar(50) NULL, /* BGN03 */

   `TransactionSetCreationTime` varchar(50) NULL, /* BGN04 */

  /* 1000A - NM1 - Information Source*/

   `SourceIdentifierCode` char(3) NULL, /* NM101 */

   `SourceType`char(3) Null, /* NM102 */

   `SourceName` varchar(60) NULL, /* NM103 */

   `SourceFirstName` varchar(35) NULL, /* NM104 */

   `SourceMiddleName` varchar(25) NULL, /* NM105 */

   `SourceSuffix` varchar(10) NULL, /* NM106 */

   `SourceIDQual` char(2) NULL, /* NM108 */

   `SourceID` varchar(30) NULL, /* NM109 */

   `SourceRelationship`char(2) NULL, /* NM110 */

   `SourceIdentifierTypeCode`char(2), /* NM111 */

  /* 1000A - PER - Source Contact Information*/

   `ContactName`varchar(50) NULL, /* PER02 */

   `ContactTelefone`varchar(20) NULL, /* one of PER03/04, PER05/06, PER07/08 or PER09/10 */

   `ContactFax`varchar(20) NULL, /* one of PER03/04, PER05/06, PER07/08 or PER09/10 */

   `ContactEmail`varchar(50) NULL, /* one of PER03/04, PER05/06, PER07/08 or PER09/10 */

   `ContactExtension`varchar(20) NULL, /* one of PER03/04, PER05/06, PER07/08 or PER09/10 */

  /* 1000A - REF - Source */

   `SourceSecondaryIDQual`varchar(50) NULL, /* REF01 */

   `SourceSecondaryID`varchar(50) NULL, /* REF02 */

  /* 1000B - NM1 - Receiver */

   `ReceiverIdentifierCode` char(3) NULL, /* NM101 */

   `ReceiverType`char(3) Null, /* NM102 */

   `ReceiverName` varchar(60) NULL, /* NM103 */

   `ReceiverFirstName` varchar(35) NULL, /* NM104 */

   `ReceiverMiddleName` varchar(25) NULL, /* NM105 */

   `ReceiverSuffix` varchar(10) NULL, /* NM106 */

   `ReceiverIDQual` char(2) NULL, /* NM108 */

   `ReceiverID` varchar(30) NULL, /* NM109 */

   `ReceiverRelationship`char(2) NULL, /* NM110 */

   `ReceiverIdentifierTypeCode`char(2), /* NM111 */

  /* 1000B - PER - Receiver */

   `ReceiverFreeFormName` varchar(50) NULL, /* PER02 */

  /* 1000B - REF - Receiver */

   `ReceiverSecondaryIDQual`varchar(50) NULL, /* REF01 */

   `ReceiverSecondaryID`varchar(50) NULL, /* REF02 */

 

  /*1000C - NM1 - Patient*/

   `PatientIdentifierCode`char(3) NULL, /* NM101 */

   `PatientType`char(1) NULL, /* NM102 */

   `PatientLastName` varchar(60) NULL, /* NM103 */

   `PatientFirstName` varchar(35) NULL, /* NM104 */

   `PatientMiddleName` varchar(25) NULL, /* NM105 */

   `PatientSuffix` varchar(10) NULL, /* NM106 */

   `PatientIDType` char(2) NULL, /* NM108 */

   `PatientID` varchar(30) NULL, /* NM109 */

 

  /* 1000C - REF1 - Receiver */

   `PatientAccountNumber`varchar(50) NULL, /* REF02 */

  /* 1000C - REF2 - Receiver */

   `EventTrackingNumber`varchar(50) NULL, /* REF02 */

     

PRIMARY KEY (`ID`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 

3.StoredPatient
 
CREATE TABLE `StoredPatient`(

   `ID` int(11) AUTO_INCREMENT NOT NULL,

 

  /*1000C - NM1 - Patient*/

   `PatientIdentifierCode`char(2) NULL, /* NM101 */

   `PatientType`char(1) NULL, /* NM102 */

   `PatientLastName` varchar(60) NULL, /* NM103 */

   `PatientFirstName` varchar(35) NULL, /* NM104 */

   `PatientMiddleName` varchar(25) NULL, /* NM105 */

   `PatientSuffix` varchar(10) NULL, /* NM106 */

   `PatientIDType` char(2) NULL, /* NM108 */

   `PatientID` varchar(30) NULL, /* NM109 */

  /* 1000C - REF1 - Receiver */

   `PatientAccountNumber`varchar(50) NULL, /* REF02 */

  /* 1000C - REF2 - Receiver */

   `EventTrackingNumber`varchar(50) NULL, /* REF02 */

 

   PRIMARY KEY `PK_StoredPatient` (`ID` ASC)

)ENGINE=InnoDB DEFAULT CHARSET=latin1;
 

4.StoredReceiver
 
CREATE TABLE StoredReceiver(

   `ID` int(11) AUTO_INCREMENT NOT NULL,

  /* 1000B - NM1 - Receiver */

   `ReceiverIdentifierCode` char(2) NULL, /* NM101 */

   `ReceiverType`char(3) Null, /* NM102 */

   `ReceiverName` varchar(60) NULL, /* NM103 */

   `ReceiverFirstName` varchar(35) NULL, /* NM104 */

   `ReceiverMiddleName` varchar(25) NULL, /* NM105 */

   `ReceiverSuffix` varchar(10) NULL, /* NM106 */

   `ReceiverIDQual` char(2) NULL, /* NM108 */

   `ReceiverID` varchar(30) NULL, /* NM109 */

   `ReceiverRelationship`char(2) NULL, /* NM110 */

   `ReceiverIdentifierTypeCode`char(2), /* NM111 */

  /* 1000B - PER - Receiver */

   `ReceiverFreeFormName` varchar(50) NULL, /* PER02 */

  /* 1000B - REF - Receiver */

   `ReceiverSecondaryIDQual`varchar(50) NULL, /* REF01 */

   `ReceiverSecondaryID`varchar(50) NULL, /* REF02 */

     

   PRIMARY KEY `PK_StoredReceiver` (`ID` ASC)

)ENGINE=InnoDB DEFAULT CHARSET=latin1;
 

5.StoredSource
 
CREATE TABLE StoredSource(

   `ID` int(11) AUTO_INCREMENT NOT NULL,

 

  /* 1000A - NM1 - Information Source*/

   `SourceIdentifierCode` char(3) NULL, /* NM101 */

   `SourceType`char(3) Null, /* NM102 */

   `SourceName` varchar(60) NULL, /* NM103 */

   `SourceFirstName` varchar(35) NULL, /* NM104 */

   `SourceMiddleName` varchar(25) NULL, /* NM105 */

   `SourceSuffix` varchar(10) NULL, /* NM106 */

   `SourceIDQual` char(2) NULL, /* NM108 */

   `SourceID` varchar(30) NULL, /* NM109 */

   `SourceRelationship`char(2) NULL, /* NM110 */

   `SourceIdentifierTypeCode`char(2), /* NM111 */

  /* 1000A - PER - Source Contact Information*/

   `ContactName`varchar(50) NULL, /* PER02 */

   `ContactTelefone`varchar(20) NULL, /* one of PER03/04, PER05/06, PER07/08 or PER09/10 */

   `ContactFax`varchar(20) NULL, /* one of PER03/04, PER05/06, PER07/08 or PER09/10 */

   `ContactEmail`varchar(50) NULL, /* one of PER03/04, PER05/06, PER07/08 or PER09/10 */

   `ContactExtension`varchar(20) NULL, /* one of PER03/04, PER05/06, PER07/08 or PER09/10 */

  /* 1000A - REF - Source */

   `SourceSecondaryIDQual`varchar(50) NULL, /* REF01 */

   `SourceSecondaryID`varchar(50) NULL, /* REF02 */

     

   PRIMARY KEY `PK_StoredSource` (`ID` ASC)

)ENGINE=InnoDB DEFAULT CHARSET=latin1;