Training Operations

It creates all of the Warehouse Tables and then creates the segment data tables based on the HL7 Segments defined in the vendor definition. When it does this it uses some internal algorithms to make a "best guess" as to how large each column should be based on the individual field definitions within each segment. The reason it makes a "best guess" is because in HL7 2.xx each HL7 Field has a "suggested size" but that encompasses the entire field. For instance take the patient address in the PID segment. HL7 rules (version 2.5.1) suggest that you should allow 250 characters for this field, BUT that field is a HL7 Data Type XAD which is made up of 14 components. This means that to follow the "suggestion" we have to create 14 columns which make up a total of 250 characters, but how to divide those characters up??? Component 1 (Street Address 1) should obviously be given a larger column size than Component 10 (Census Tract). I think you get the idea of what we mean when we say "best guess". So how do we do it? Well, we don't pay strict attention to the "suggested size", but rather use it as a starting point.

Column creation is always a tradeoff, we want your columns to have enough space to store your HL7 data without truncation but we also can't go crazy and just make every column a Varchar(1024) or Varchar(Max) because a customer might want to actually create usable indexes on some of these fields without bogging down their SQL Server.

We follow some rules. The Schema Engine (or the API in training operations) will NOT create a column smaller than a Varchar(30) even when the HL7 Definition indicates that the field is only a 1 character flag field. Likewise, it will NOT create a data column larger than Varchar(512), opting instead to create those columns as Varchar(Max).1

In general the Schema Engine does a very good job with this and the basic schema creation is usually good for all customer needs. However, it is inevitable that some issues will arise based on the data needs in the real world and the vagaries of HL7 trading partners that regard the HL7 standards as more like loose "guidelines" to be flouted at their will.

1 Prior versions of the SQL Schema Engine (before version 6.5) did not support the Varchar(MAX) column at all and would not create a column smaller than Varchar(10) and columns larger than Varchar(254) were created as Text columns.

1.They could modify their HL7 vendor definition and tweak field sizes with modified data types etc. The downside of this was that it was easy to cause unexpected side effects. Example: If  a Laboratory is passing you an extra component in the Patient Name field that indicates whether the patient has been fasting for the past 24 hours (that IS a real world example) and to accommodate it you modify the HL7 data type of the Patient Name (XPN) and add an additional component to hold that flag. Easy right? Wrong. If you did that you had then modified EVERY person field in HL7, and there are hundreds of them, so that the Gurantor Name and Mothers Maiden Name in the IN1 segment now also would have this "Patient Fasting" flag. So to avoid that you had to create a NEW data type that was a clone of XPN and then add the flag to THAT data type and then change the data type associated with Patient Name in the PID segment (field #5) to use the new data type and then recreate your Schema Tables. WOW, that's a lot!

2.You could always go into SQL Server management studio and manually add a new component field to the PID table (Example PID_F5_C15) and the Schema Engine would then (after a service restart) automatically detect the new field and would populate it. The downside of that is that you:

A.Had to be VERY careful creating a new field like this. Must be varchar(n) must Allow Null, the name must be exactly right etc.

B.You had REMEMBER that you did this so that if you EVER had to recreate your Schema Tables you would then know to go in and add the column again. So we would always advise customers to script changes like this and then keep the scripts to reuse if they had to recreate their schema.