Recommended approach to merging two tables

Question

I have a database schema like this:

[Patients]                    [Referrals]
     |                             |
[PatientInsuranceCarriers]    [ReferralInsuranceCarriers]
                        \        /
                    [InsuranceCarriers]

PatientInsuranceCarriers and ReferralInsuranceCarriers are identical, except for the fact that they reference either Patients or Referrals. I would like to merge those two tables, so that it looks like this:

[Patients]      [Referrals]
       \           /
  [PatientInsuranceCarriers]
              |
      [InsuranceCarriers]

I have two options here

• either create two new columns - ID_PatientOrReferral + IsPatient (will tell me which table to reference)
• or create two different columns - ID_Patient and ID_Referral, both nullable.

Generally, I try to avoid nullable columns, because I consider them a bad practice (meaning, if you can live w/o nulls, then you don't really need a nullable column) and they are more difficult to work with in code (e.g., LINQ to SQL).

However I am not sure if the first option would be a good idea. I saw that it is possible to create two FKs on ID_PatientOrReferral (one for Patients and one for Referrals), though I can't set any update/delete behavior there for obvious reasons, I don't know if constraint check on insert works that way, either, so it looks like the FKs are there only to mark that there are relationships. Alternatively, I may not create any foreign keys, but instead add the relationships in DBML manually.

Is any of the approaches better and why?

Answer 1

Copying my answer from this question

If you really need A_or_B_ID in TableZ, you have two similar options:

1) Add nullable A_ID and B_ID columns to table z, make A_or_B_ID a computed column using ISNULL on these two columns, and add a CHECK constraint such that only one of A_ID or B_ID is not null

2) Add a TableName column to table z, constrained to contain either A or B. now create A_ID and B_ID as computed columns, which are only non-null when their appropriate table is named (using CASE expression). Make them persisted too

In both cases, you now have A_ID and B_ID columns which can have appropriate foreign keys to the base tables. The difference is in which columns are computed. Also, you don't need TableName in option 2 above if the domains of the 2 ID columns don't overlap - so long as your case expression can determine which domain A_or_B_ID falls into

Answer 2

To expand on my somewhat terse comment:

I would like to merge those two tables

I believe this would be a bad idea. At the moment you have two tables with good clear relation predicates (briefly, what it means for there to exist a record in the table) - and crucially, these relation predicates are different for the two tables:

• A record exists in PatientInsuranceCarriers <=> that Patient is associated with that Insurance Carrier

• A record exists in ReferralInsuranceCarriers <=> that Referral is associated with that Insurance Carrier

Sure, they are similar, but they are not the same. Consider now what would be the relation predicate of a combined table:

• A record exists in ReferralAndPatientInsuranceCarriers <=> {(IsPatient is true and the Patient with ID ID_PatientOrReferral) or alternatively (IsPatient is false and the Referral with ID ID_PatientOrReferral)} is associated with that Insurance Carrier

or if you do it with NULLs

• A record exists in ReferralAndPatientInsuranceCarriers <=> {(ID_Patient is not NULL and the Patient with ID ID_Patient) or alternatively (ID_Referral is not NULL and the Referral with ID ID_Referral)} is associated with that Insurance Carrier

Now, I'm not one to automatically suggest that more complicated relation pedicates are necessarily worse; but I'm fairly sure that either of the two above are worse than those they would replace.

---

To address your concerns:

we now have two LINQ to SQL entities, separate controllers and views for each

In general I would agree with reducing duplication; however, only duplication of the same things! Here, is it not the case that all the above are essentially 'boilerplate', and their construction and maintenance can be delegated to suitable development tools?

and have to merge them when preparing data for reports

If you were to create a VIEW, containing a UNION, for reporting purposes, you would keep the simplicity of the actual data and still have the ability to report on a combined list; eg (making assumptions about column names etc):

CREATE VIEW InterestingInsuranceCarriers
AS
SELECT 
    IC.Name InsuranceCarrierName
    , P.Name CounterpartyName
    , 'Patient' CounterpartyType
FROM InsuranceCarriers IC
    INNER JOIN PatientInsuranceCarriers PIC ON IC.ID = PIC.InsuranceCarrierID
    INNER JOIN Patient P ON PIC.PatientId = P.ID
UNION
SELECT 
    IC.Name InsuranceCarrierName
    , R.Name CounterpartyName
    , 'Referral' CounterpartyType
FROM InsuranceCarriers IC
    INNER JOIN ReferralInsuranceCarriers RIC ON IC.ID = RIC.InsuranceCarrierID
    INNER JOIN Referral R ON PIC.ReferralId = R.ID