Complex (Multi-Schema) Entity Framework object map

Question

I am a fairly novice, enthusiast programmer. This is my first question, but I have been using stackoverflow for valuable information for several months now.

First, some context:

My current, somewhat jack-of-all trades job in an extremely small (<10 employees) specialist physician's office has put me in a unique position where I have free reign (and moderate resource backing) to develop and implement any kind of application/tool without any kind of demand or pressure as the current system functions well enough.

We currently run a fairly dated (circa ~2008) medical office management system that takes care of patient business accounts, billing, and insurance claim submissions. The office itself is fairly hap-hazardly networked. We have a couple of diagnostic machines that use standard DICOM, but the majority of medical records remains paper.

I may have bit off more than I can currently chew with my aspirations, but I have plans to slowly develop a more all-encompassing, domain-driven application that couples electronic medical records (evaluation/management and DICOM diagnostics) with the medical office management end. Once I have the framework of such an application laid, the sandbox aspect of my situation entices me in that I can explore and develop any kind of automation or tool I can dream.

Some honesty:

My experience is very limited in such things, but I am a very determined individual and I love application-driven learning.

My question:

I am working on first integrating with the current database of patient accounts.

Everything is currently stored in FoxPro 2.5 free tables. The relationships between the tables are not implicitly obtainable but quite a few are implied. Ideally I would like to have my new application with POCOs mapped with EF to an SQL database. That part is straightforward enough, but I would also like to have the same POCOs mapped to the current FoxPro 2.5 .dbfs (which won't necessarily have the same schema) in a separate DBContext.

Is such a thing possible as I am envisioning? I have been testing the waters of deriving all of the fluentAPI mappings (.ToTable(), .HasColumnName(), etc.) but it is a fairly daunting task and I would like some more experienced insight before dive head-first. I have been unable to find any relevant examples of anyone pulling off what I am attempting, which is somewhat discouraging as well.

Perhaps my approach is wrong. I am willing to adjust accordingly, but I like the idea of working with POCOs in my application and it is pretty important for my new application to be able to talk to the old database without implementing its unintuitive schema.

The key purpose of all the headache is to keep the current application fully functional while concurrently allowing me to run and develop my new application.

So in a nutshell:

Is it possible to use EF to integrate a new OOR/Domain-driven application with an old database of mildly different schema? If so, any tips or examples to get me started? If not, do I have any other functionally-similar alternatives? Thanks

---

Edit 1:

I'm going to refer to the currently used application as App X from now on.

App X's predecessor ran on Unix and sat on FoxPro/xBASE tables as well so App X was built on top of that presumably to trivialize customers upgrading. The App X directory also contains Visual Fox Pro 6 .dlls and an application file with a FoxPro .ico and the name "fbaseeng" which brings up a command prompt window titled "DTS Command Prompt". I don't know for sure how App X ticks, but 'DTS' stuck out to me and I spent some time seeing if there was a way I could use whatever data transformation they already implemented, but I eventually gave up.

The current database is a collection of 231 .dbf tables. Luckily, a good portion of them are seemingly either unused altogether, or only used in some round-about, temporary way where they don't store records outside the run-time of App X.

Several of the tables seem to be linking tables and another portion of them contain reference data like Type qualifier properties

public partial class Accttype
{
    public decimal Acct_Type { get; set; }
    public string Acct_Desc { get; set; }
    public string Sb { get; set; }
    public decimal Fee { get; set; }
    public bool Acptasgn { get; set; }
    public decimal Insclass1 { get; set; }
    public decimal Insclass2 { get; set; }
    public decimal Insclass3 { get; set; }
    public decimal Insclass4 { get; set; }
    public decimal Insclass5 { get; set; }
    public string Acct_Grp { get; set; }
}

and static reference values like zip codes

public partial class Zip
{
    public string Zipcode { get; set; }
    public string City { get; set; }
    public string St { get; set; }
    public string Areacode { get; set; }
    public decimal Ext_From { get; set; }
    public decimal Ext_To { get; set; }
}

So far, I am most concerned with the following tables:

- Patdemo.dbf Contains records for all of the patients to visit the practice. It has around 100 columns and contains a plethora of information including name, address, insurance type(s), running account balance totals, etc. Straightforward primary key that is patient ID, but the format is '0.0'.

- Billing.dbf Contains alpha-numeric ID-indexed bills associated with patients on a specific date of service. Has about 80 columns including mostly foreign keys/type qualifiers and status indicators (i.e. INS1_SENT). Has FK of patient ID

- Charges.dbf Contains line items that fall under each Bill. It is either a table per inheritance or a join of Charges and Postings/Payments as it contains both records indicated by C or P in a Type column. There doesn't seem to be a simple primary key, but Charges have ChargeID and Postings/Payments have PostID, both with format BillID+"000N". To throw a curveball, however, voiding adjustments have no ChargeID/PostID. Has FK of BillID.

- Insur.dbf Contains insurance providers and information from address to electronic billing ID. Primary key is alpha-numeric ICode (example: BC01).

- Patins.dbf Seems to be a linking table, but also contains information like the patient's insurance ID number for the policy. Has FKs of patient ID and ICode.

There are various other referential tables I will want to keep concurrency with (like diagnoses, referring doctors, and CPT codes), but they are lower priority right now.

I haven't completely designed my new application's schema yet, but I do know it will be far more logical in that things like Addresses will be a concrete type regardless of if associated with a patient or insurance company.

For sake of this example, let's look at the pre-existing Patins.dbf POCO (it is one of the smallest tables):

public partial class Patins
{
    public decimal Custid { get; set; }
    public decimal Inskey { get; set; }
    public string Insurcode { get; set; }
    public string Insurnum { get; set; }
    public string Groupnum { get; set; }
    public string Guarlname { get; set; }
    public string Guarfname { get; set; }
    public string Guarmi { get; set; }
    public string Guargen { get; set; }
    public string Guaraddr { get; set; }
    public string Guaraddr2 { get; set; }
    public string Guarcity { get; set; }
    public string Guarst { get; set; }
    public string Guarzip { get; set; }
    public string Guarcountr { get; set; }
    public string Guarphone { get; set; }
    public string Guaremail { get; set; }
    public System.DateTime Guardob { get; set; }
    public string Guarsex { get; set; }
    public string Guaremp { get; set; }
    public decimal Relation { get; set; }
    public System.DateTime Startdate { get; set; }
    public System.DateTime Enddate { get; set; }
    public bool Active { get; set; }
    public string Bcpc { get; set; }
    public string Auth1 { get; set; }
    public string Auth2 { get; set; }
    public string Auth3 { get; set; }
    public decimal Billcnt { get; set; }
    public string Desc1 { get; set; }
    public string Desc2 { get; set; }
    public string Desc3 { get; set; }
    public decimal Visits1 { get; set; }
    public decimal Visits2 { get; set; }
    public decimal Visits3 { get; set; }
    public System.DateTime From1 { get; set; }
    public System.DateTime From2 { get; set; }
    public System.DateTime From3 { get; set; }
    public System.DateTime To1 { get; set; }
    public System.DateTime To2 { get; set; }
    public System.DateTime To3 { get; set; }
    public string Insnote { get; set; }
    public string Char1 { get; set; }
    public string Char2 { get; set; }
    public string Char3 { get; set; }
    public string Char4 { get; set; }
    public string Char5 { get; set; }
    public string Char6 { get; set; }
    public string Char7 { get; set; }
    public string Char8 { get; set; }
    public string Char9 { get; set; }
    public string Char10 { get; set; }
    public System.DateTime Date1 { get; set; }
    public System.DateTime Date2 { get; set; }
    public decimal Num1 { get; set; }
    public decimal Num2 { get; set; }
    public string Createby { get; set; }
    public System.DateTime Createdt { get; set; }
    public string Modifyby { get; set; }
    public System.DateTime Modifydt { get; set; }
    public string Cobmemo { get; set; }
    public System.DateTime Dinju { get; set; }
    public System.DateTime Dsimbd { get; set; }
    public System.DateTime Dsimed { get; set; }
    public string Createtm { get; set; }
    public string Modifytm { get; set; }
    public bool Archive { get; set; }
    public bool Delflag { get; set; }
    public decimal Coinsded { get; set; }
    public decimal Outpoc { get; set; }
    public System.DateTime Lastupd { get; set; }
    public decimal Coins { get; set; }
    public decimal Msp { get; set; }
}

In the real world, a patient is associated with an insurance company by way of insurance policy. There is a FK_PatientID, FK_InsuranceCarrierID, and unique ID, PK_PolicyNumber (possibly PolicyNumber+InsuranceCarrierID to be safe?). Policies have benefits information that dictate payment, and spouses/families can share policies (usually appending -0n to policy number).

I will probably let Patient objects contain a collection of insurance policy objects. Along these lines:

class Patient : Person
{
    int PatientID { get; set; }
    virtual IEnumerable<InsurancePolicy> InsurancePolicies { get; set; }
}

class InsurancePolicy
{
    int PatientID { get; set; }
    string PolicyNumber { get; set; }
    string GroupNumber { get; set; }
    bool IsActive { get; set; }
    DateTime startDate { get; set; }
    DateTime endDate { get; set; }
    int InsuranceCarrierID { get; set; }
    virtual Person Guarantor { get; set; } //all guarantor information accessible via person aggregate root i.e: Guarantor.FirstName
    string GuarantorRelation { get; set; }
    string[] Benefits { get; set; }  //delineated set of benefit descriptions... automatically parse from EDI benefits message?... seperate value object class?... could contain copay/deduc/OoP
    decimal Deductible { get; set; }
    decimal Copay { get; set; }
    decimal OutofPocket { get; set; }
    virtual IEnumerable<Bill> AssociatedBills { get; set; } //all bills associated with policy... could also be InsuranceClaim objects... Automapper Bill->Claim?
}

There are a few other things that need to be represented either in InsurancePolicy or elsewhere like pay rate percentage, but I am leaving them off for now.

My question finally arrives when looking for ways to map the data to/from the old FP Tables. Specifically looking at Guarantor: as a Person object, it will be stored in an inheritance table in the SQL schema so what is the best way to go about mapping? Simply .ToTable("Patins") in the InsurancePolicyMap with (t => t.Guarantor.FirstName).HasColumnName("Guarfname") seems logical, but does EF take care of the separate relationship patterns automatically? Possibly better worded: Does it have trouble navigating the relationship/inheritance that lies between the physical Person.FirstName, the SQLMap, InsurancePolicy.Guarantor.FirstName, the VFPmap, and the physical Patins.Guarfname?

What about Billcnt? It is luckily not implemented in App X for whatever reason, but what would the mapping be for what I assume would be AssociatedBills.Count()? Would you just check validity pulling the value from the FP Table?

Answer 1

Since you have "free reign" I would take the chance to upgrade the whole system. This is a very very poor data model and it's really no use to drag this legacy along any time longer. These nondescript repetitive numbered fields will be a continuous source of confusion and bugs. And it will be virtually impossible to build a decent domain from it. Entity Framework has plenty of options to shape the class model differently that the data model, but this would be too much. Really, it's useless to rebuild the application while keeping the data model.

You should definitely normalize the data model. Create tables like Insclass with a foreign key to Accttype and so on and so forth. Also a Benefit table with an FK to InsurancePolicy as you can't map a string array to a database column.

But first (and foremost) get the requirements clear. It sounds good to have total liberty to build "any kind of application", but users always have something in mind. I would take ample time to pick their brains before you even type one line of code. Agree upon the things to do first. Then start building the application use case after use case. And let them test each use case. This will give them time to get used to the new system and slowly detach from the old system, and to fine-tune requirements as you go. (This is agile development in a nutshell).