Creating Tables

Top Previous Next

Before data export, you need to create the database tables to which the application will export the enrollment data. You can create the tables using the built-in function of the HIPAA Enrollment Master.

After you have defined the connection parameters and tested that the data connection works (see Setting up Database Connection), you can create the tables. Follow the instructions below.

1.	Select the Database Table Creation in the main menu. The "Table Creation" menu

2.	The following window will be displayed. The "Create SQL Tables" window This window allows you to edit, save and run the table scripts.

3.	Define the following options:

•

Database

•	Microsoft SQL Server

•

MySQL

•	Enrollments Table Script — SQL statements to create the Enrollment table in your database.

•	Benefits Table Script — SQL statements to create the Benefits table in your database.

•	Reporting Categories Script - SQL statement to create the Reporting Categories table

Tip: You can modify the scripts so that they run on your specific database.

modify_table
The "Create SQL Tables" window

Once you have modified the script, click "Save."

save_button
The "Save" button

For each script, click "Execute Script" to create the corresponding table in the database.

execute_button
The "Execute Script" button

Once the table has been created successfully, you will see the following notification:

table8
The success message

Warning: Executing the scripts will wipe out any previous tables that you created. Remove the script files once you are satisfied so nobody can destroy the tables by accident.

Make sure there are no any error messages for successful table creation.

Understanding and Altering the Scripts

The scripts come from two files (EDI_Enrollment.sql and EDI_Benefits.sql) that are distributed with the application.

Scripts for two databases are supplied:

•	MS SQL Server

•	MySQL - The open source database.

Below are the scripts for Microsoft SQL Server. The supplied SQL script files can be modified to conform to your database specific SQL syntax. If you have other databases, modify the supplied scripts to conform to the specific database syntax. Please, contact HIPAAsuite if you need help with the database setup.

The tables are created using the SQL "CREATE TABLE" statement. Both tables have an ID column that is self incrementing. The field EnrollmentID in the child table is the foreign key and points to the ID in the header table.

CREATE TABLE [dbo].[EDI_Enrollment] (

[ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL,

[TradingPartnerID] [char] (15) NULL,

[EDIFileName] [varchar] (100) NULL,

[ImageFilePath] [varchar](300) NULL,

[ImageFileName] [varchar] (100) NULL,

[LoadDate] [date] NULL,

[Purpose] [char] (2) NULL,

[TransactionID] [varchar] (50) NULL,

[TransactionDate] [varchar] (12) NULL,

[TransactionTime] [varchar] (8) NULL,

[Action] [varchar] (2) NULL,

[MasterPolicyNumber] [varchar] (80) NULL,

[EffectiveDate] [date] NULL,

[ReportStartDate] [date] NULL,

[ReportEndDate] [date] NULL,

[MaintenanceEffectiveDate] [date] NULL,

[EnrollmentDate] [date] NULL,

[PaymentCommencementDate] [date] NULL,

[Sponsor] [varchar] (60) NULL,

[SponsorIDType] [varchar] (2) NULL,

[SponsorID] [varchar] (80) NULL,

[Payer] [varchar] (60) NULL,

[PayerIDType] [varchar] (2) NULL,

[PayerID] [varchar] (80) NULL,

[BrokerEntityCode] [char] (3) NULL,

[Broker] [varchar] (60) NULL,

[BrokerIDType] [varchar] (2) NULL,

[BrokerID] [varchar] (80) NULL,

[BrokerAccount] [varchar] (35) NULL,

[BrokerAccount2] [varchar] (35) NULL,

[SubscriberIndicator] [varchar] (1) NULL,

[Relationship] [char] (2) NULL,

[MaintenanceType] [char] (3) NULL,

[MaintenanceReason] [char] (3) NULL,

[BenefitStatus] [char] (1) NULL,

[MedicarePlanCode] [char] (1) NULL,

[MedicareEligibilityReason] [char] (1) NULL,

[COBRACode] [char] (2) NULL,

[EmploymentStatus] [char] (2) NULL,

[StudentStatus] [char] (1) NULL,

[HandicapIndicator] [varchar] (1) NULL,

[DeathDate] [varchar] (12) NULL,

[ConfidentialityCode] [char] (1) NULL,

[BirthSequence] [varchar] (3) NULL,

[IncomeFrequency] [char] (1) NULL,

[WageAmount] [numeric](8, 2) NULL,

[HoursWorked] [numeric](3, 0) NULL,

[WorkLocation] [varchar] (30) NULL,

[SalaryGrade] [varchar] (5) NULL,

[SubscriberNumber] [varchar] (50) NULL,

[PolicyNumber] [varchar] (50) NULL,

[MemberIDType1] [char] (3) NULL,

[MemberID1] [varchar] (50) NULL,

[MemberIDType2] [char] (3) NULL,

[MemberID2] [varchar] (50) NULL,

[MemberIDType3] [char] (3) NULL,

[MemberID3] [varchar] (50) NULL,

[MemberIDType4] [char] (3) NULL,

[MemberID4] [varchar] (50) NULL,

[MemberIDType5] [char] (3) NULL,

[MemberID5] [varchar] (50) NULL,

[MemberIDType6] [char] (3) NULL,

[MemberID6] [varchar] (50) NULL,

[MemberIDType7] [char] (3) NULL,

[MemberID7] [varchar] (50) NULL,

[MemberIDType8] [char] (3) NULL,

[MemberID8] [varchar] (50) NULL,

[MemberIDType9] [char] (3) NULL,

[MemberID9] [varchar] (50) NULL,

[MemberIDType10] [char] (3) NULL,

[MemberID10] [varchar] (50) NULL,

[MemberIDType11] [char] (3) NULL,

[MemberID11] [varchar] (50) NULL,

[MemberIDType12] [char] (3) NULL,

[MemberID12] [varchar] (50) NULL,

[MemberIDType13] [char] (3) NULL,

[MemberID13] [varchar] (50) NULL,

[PriorCoverageMonths] [varchar] (50) NULL,

[DateType1] [varchar] (3) NULL,

[Date1] [varchar] (12) NULL,

[DateType2] [varchar] (3) NULL,

[Date2] [varchar] (12) NULL,

[DateType3] [varchar] (3) NULL,

[Date3] [varchar] (12) NULL,

[MemberLastName] [varchar] (60) NULL,

[MemberFirstName] [varchar] (35) NULL,

[MemberMiddleName] [varchar] (25) NULL,

[MemberIDType] [varchar] (3) NULL,

[MemberID] [varchar] (50) NULL,

[CommQual1] [varchar] (2) NULL,

[CommunicationNumber1] [varchar] (80) NULL,

[CommQual2] [varchar] (2) NULL,

[CommunicationNumber2] [varchar] (80) NULL,

[MemberAddress1] [varchar] (55) NULL,

[MemberAddress2] [varchar] (55) NULL,

[MemberCity] [varchar] (30) NULL,

[MemberState] [varchar] (2) NULL,

[MemberZip] [varchar] (15) NULL,

[MemberCountry] [varchar] (3) NULL,

[MemberArea] [varchar] (30) NULL,

[MemberCounty] [varchar] (30) NULL,

[MemberSubdivision] [varchar] (3) NULL,

[MemberBirthday] [varchar] (12) NULL,

[MemberSex] [varchar] (1) NULL,

[MemberMaritialStatus] [varchar] (50) NULL,

[MemberRace1] [varchar] (3) NULL,

[MemberRace2] [varchar] (30) NULL,

[MemberRaceCollection] [varchar] (30) NULL,

[MemberCitizenshipStatus] [varchar] (2) NULL,

[CoInsurance] [money] NULL,

[CoPayment] [money] NULL,

[Deductible] [money] NULL,

[Premium] [money] NULL,

[HealthRelatedCode] [char] (1) NULL,

[Height] [varchar] (6) NULL,

[Weight] [varchar] (10) NULL,

[LanguageCodeList] [varchar] (2) NULL,

[Language] [varchar] (3) NULL,

[LanguageDescription] [varchar] (80) NULL,

[LanguageUse] [varchar] (2) NULL,

[IncorrectLastName] [varchar] (60) NULL,

[IncorrectFirstName] [varchar] (35) NULL,

[IncorrectMiddleName] [varchar] (25) NULL,

[IncorrectIDType] [varchar] (2) NULL,

[IncorrectID] [varchar] (30) NULL,

[IncorrectBirthday] [varchar] (12) NULL,

[IncorrectSex] [varchar] (1) NULL,

[MailingAddress1] [varchar] (55) NULL,

[MailingAddress2] [varchar] (55) NULL,

[MailingCity] [varchar] (30) NULL,

[MailingState] [varchar] (2) NULL,

[MailingZip] [varchar] (15) NULL,

[MailingCountry] [varchar] (3) NULL,

[MailingSubdivision] [varchar] (3) NULL,

[EmployerName] [varchar] (60) NULL,

[EmployerID] [varchar] (50) NULL,

[EmployerCommQual1] [varchar] (2) NULL,

[EmployerCommNumber1] [varchar] (50) NULL,

[EmployerCommQual2] [varchar] (2) NULL,

[EmployerCommNumber2] [varchar] (50) NULL,

[EmployerAddress1] [varchar] (55) NULL,

[EmployerAddress2] [varchar] (55) NULL,

[EmployerCity] [varchar] (30) NULL,

[EmployerState] [varchar] (2) NULL,

[EmployerZip] [varchar] (15) NULL,

[EmployerCountry] [varchar] (3) NULL,

[EmployerSubdivision] [varchar] (3) NULL,

[SchoolName] [varchar] (60) NULL,

[SchoolCommQual1] [varchar] (2) NULL,

[SchoolCommNumber1] [varchar] (50) NULL,

[SchoolCommQual2] [varchar] (2) NULL,

[SchoolCommNumber2] [varchar] (50) NULL,

[SchoolAddress1] [varchar] (55) NULL,

[SchoolAddress2] [varchar] (55) NULL,

[SchoolCity] [varchar] (30) NULL,

[SchoolState] [varchar] (2) NULL,

[SchoolZip] [varchar] (15) NULL,

[SchoolCountry] [varchar] (3) NULL,

[SchoolSubdivision] [varchar] (3) NULL,

[CustodianLastName] [varchar] (60) NULL,

[CustodianFirstName] [varchar] (35) NULL,

[CustodianMiddleName] [varchar] (25) NULL,

[CustodianIDType] [char] (2) NULL,

[CustodianID] [varchar] (80) NULL,

[CustodianCommQual1] [varchar] (2) NULL,

[CustodianCommNumber1] [varchar] (50) NULL,

[CustodianCommQual2] [varchar] (2) NULL,

[CustodianCommNumber2] [varchar] (50) NULL,

[CustodianAddress1] [varchar] (55) NULL,

[CustodianAddress2] [varchar] (55) NULL,

[CustodianCity] [varchar] (30) NULL,

[CustodianState] [varchar] (2) NULL,

[CustodianZip] [varchar] (15) NULL,

[CustodianCountry] [varchar] (3) NULL,

[CustodianSubdivision] [varchar] (3) NULL,

[ResponsibleType] [varchar] (3) NULL,

[ResponsibleLastName] [varchar] (60) NULL,

[ResponsibleFirstName] [varchar] (35) NULL,

[ResponsibleMiddleName] [varchar] (25) NULL,

[ResponsibleIDType] [varchar] (2) NULL,

[ResponsibleID] [varchar] (30) NULL,

[ResponsibleCommQual1] [varchar] (3) NULL,

[ResponsibleCommNumber1] [varchar] (50) NULL,

[ResponsibleCommQual2] [varchar] (3) NULL,

[ResponsibleCommNumber2] [varchar] (50) NULL,

[ResponsibleAddress1] [varchar] (55) NULL,

[ResponsibleAddress2] [varchar] (55) NULL,

[ResponsibleCity] [varchar] (30) NULL,

[ResponsibleState] [varchar] (2) NULL,

[ResponsibleZip] [varchar] (10) NULL,

[ResponsibleCountry] [varchar] (3) NULL,

[ResponsibleSubdivision] [varchar] (3) NULL,

[DropOffLocationLastName] [varchar] (60) NULL,

[DropOffLocationFirstName] [varchar] (35) NULL,

[DropOffLocationMiddleName] [varchar] (25) NULL,

[DropOffLocationAddress1] [varchar] (55) NULL,

[DropOffLocationAddress2] [varchar] (55) NULL,

[DropOffLocationCity] [varchar] (30) NULL,

[DropOffLocationState] [varchar] (2) NULL,

[DropOffLocationZip] [varchar] (10) NULL,

[DropOffLocationCountry] [varchar] (3) NULL,

[DropOffLocationSubdivision] [varchar] (3) NULL,

[DisabilityType] [varchar] (3) NULL,

[DisabilityDiagnosisQual] [varchar] (2) NULL,

[DisabilityDiagnosis] [varchar] (20) NULL,

[DisabilityBegin] [varchar] (12) NULL,

[DisabilityEnd] [varchar] (12) NULL,

CONSTRAINT [PK_EDI_Enrollment] PRIMARY KEY CLUSTERED

(

[ID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

CREATE TABLE [dbo].[EDI_Benefits] (

[ID] [bigint] IDENTITY (1, 1) NOT NULL,

[EnrollmentID] [numeric](18, 0) NOT NULL,

[MaintenanceType] [varchar] (50) NULL,

[InsuranceLine] [varchar] (50) NULL,

[Description] [varchar] (50) NULL,

[CoverageLevel] [varchar] (3) NULL,

[ConditionResponseCode] [varchar] (1) NULL,

[SignatureDate] [varchar] (12) NULL,

[EffectiveDate] [varchar] (12) NULL,

[PremiumPaidtoDateEnd] [varchar] (12) NULL,

[BenefitBegin] [varchar] (12) NULL,

[BenefitEnd] [varchar] (12) NULL,

[LastPremiumPaidDate] [varchar] (12) NULL,

[PreviousPeriod] [varchar] (17) NULL,

[PolicyNumber] [varchar] (50) NULL,

[ClientReportingCategory] [varchar] (50) NULL,

[PaymentPlanType] [varchar] (50) NULL,

[CountyCode] [varchar] (50) NULL,

[PriorCoverageMonths] [varchar] (50) NULL,

[CoInsurance] [varchar] (10) NULL,

[CoPayment] [varchar] (10) NULL,

[Deductible] [varchar] (10) NULL,

[Premium] [varchar] (10) NULL,

[ProviderType] [varchar] (50) NULL,

[ProviderLastName] [varchar] (60) NULL,

[ProviderFirstName] [varchar] (35) NULL,

[ProviderMiddleName] [varchar] (25) NULL,

[ProviderIDType] [varchar] (2) NULL,

[ProviderID] [varchar] (30) NULL,

[ProviderRelationship] [varchar] (2) NULL,

[ProviderAddress1] [varchar] (55) NULL,

[ProviderAddress2] [varchar] (55) NULL,

[ProviderCity] [varchar] (30) NULL,

[ProviderState] [varchar] (2) NULL,

[ProviderZip] [varchar] (15) NULL,

[ProviderCountry] [varchar] (3) NULL,

[ProviderLocationQualifier] [char] (2) NULL,

[ProviderLocation] [varchar] (30) NULL,

[ProviderSubdivision] [varchar] (3) NULL,

[ProviderPhone] [varchar] (20) NULL,

[PCP_ChangeReason] [varchar] (2) NULL,

[PCP_ChangeDate] [varchar] (12) NULL,

[Provider2Type] [varchar] (50) NULL,

[Provider2LastName] [varchar] (60) NULL,

[Provider2FirstName] [varchar] (35) NULL,

[Provider2MiddleName] [varchar] (25) NULL,

[Provider2IDType] [varchar] (2) NULL,

[Provider2ID] [varchar] (30) NULL,

[Provider2Relationship] [varchar] (2) NULL,

[Provider2Address1] [varchar] (55) NULL,

[Provider2Address2] [varchar] (55) NULL,

[Provider2City] [varchar] (30) NULL,

[Provider2State] [varchar] (2) NULL,

[Provider2Zip] [varchar] (15) NULL,

[Provider2Country] [varchar] (3) NULL,

[Provider2LocationQualifier] [char] (2) NULL,

[Provider2Location] [varchar] (30) NULL,

[Provider2Subdivision] [varchar] (3) NULL,

[Provider2Phone] [varchar] (20) NULL,

[PCP2_ChangeReason] [varchar] (2) NULL,

[PCP2_ChangeDate] [varchar] (12) NULL,

[COB_Sequence] [varchar] (1) NULL,

[COB_Policy] [varchar] (30) NULL,

[COB_Code] [varchar] (1) NULL,

[COB_ServiceTypeCode] [varchar] (2) NULL,

[COB_GroupNo] [varchar] (50) NULL,

[COB_Type] [varchar] (2) NULL,

[COB_Name] [varchar] (50) NULL,

[COB_IDType] [char] (2) NULL,

[COB_ID] [varchar] (30) NULL,

[COB_Address1] [varchar] (55) NULL,

[COB_Address2] [varchar] (55) NULL,

[COB_City] [varchar] (30) NULL,

[COB_State] [varchar] (2) NULL,

[COB_Zip] [varchar] (15) NULL,

[COB_Country] [varchar] (3) NULL,

[COB_Subdivision] [varchar] (3) NULL,

[COB_CommQual] [varchar] (3) NULL,

[COB_CommNum] [varchar] (50) NULL,

[COB_Begin] [varchar] (12) NULL,

[COB_End] [varchar] (12) NULL,

[COB2_Sequence] [varchar] (1) NULL,

[COB2_Policy] [varchar] (30) NULL,

[COB2_Code] [varchar] (1) NULL,

[COB2_ServiceTypeCode] [varchar] (2) NULL,

[COB2_GroupNo] [varchar] (50) NULL,

[COB2_Type] [varchar] (2) NULL,

[COB2_Name] [varchar] (50) NULL,

[COB2_IDType] [char] (2) NULL,

[COB2_ID] [varchar] (30) NULL,

[COB2_Address1] [varchar] (55) NULL,

[COB2_Address2] [varchar] (55) NULL,

[COB2_City] [varchar] (30) NULL,

[COB2_State] [varchar] (2) NULL,

[COB2_Zip] [varchar] (15) NULL,

[COB2_Country] [varchar] (3) NULL,

[COB2_Subdivision] [varchar] (3) NULL,

[COB2_CommQual] [varchar] (3) NULL,

[COB2_CommNum] [varchar] (50) NULL,

[COB2_Begin] [varchar] (12) NULL,

[COB2_End] [varchar] (12) NULL,

CONSTRAINT [PK_EDI_Benefits] PRIMARY KEY CLUSTERED

(

[ID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

CREATE TABLE [dbo].[EDI_ReportingCategories] (

[ID] [bigint] IDENTITY (1, 1) NOT NULL,

[EnrollmentID] [numeric](18, 0) NOT NULL,

[Name] [varchar](60) NULL,

[ReferenceQual1] [char](3) NULL,

[ReferenceID1] [varchar](50) NULL,

[ReferenceQual2] [char](3) NULL,

[ReferenceID2] [varchar](50) NULL,

[ReferenceQual3] [char](3) NULL,

[ReferenceID3] [varchar](50) NULL,

[ReferenceQual4] [char](3) NULL,

[ReferenceID4] [varchar](50) NULL,

[ReferenceQual5] [char](3) NULL,

[ReferenceID5] [varchar](50) NULL,

[EffectiveDateStart] [date] NULL,

[EffectiveDateEnd] [date] NULL,

CONSTRAINT [PK_EDI_Categories] PRIMARY KEY CLUSTERED

(

[ID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]