Databases
StackOverflow2013
Postsdbo
PORecommended approach to merging two tables
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PORecommended approach to merging two tables
    primarykey
    data
    text
    <p>I have a database schema like this:</p> <pre><code>[Patients] [Referrals] | | [PatientInsuranceCarriers] [ReferralInsuranceCarriers] \ / [InsuranceCarriers] </code></pre> <p>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:</p> <pre><code>[Patients] [Referrals] \ / [PatientInsuranceCarriers] | [InsuranceCarriers] </code></pre> <p>I have two options here</p> <ul> <li>either create two new columns - ID_PatientOrReferral + IsPatient (will tell me which table to reference)</li> <li>or create two different columns - ID_Patient and ID_Referral, both nullable.</li> </ul> <p>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).</p> <p>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.</p> <p>Is any of the approaches better and why?</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USPawel Krakowiak
    1. USPawel Krakowiak
    plurals
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. PORecommended approach to merging two tables
      1. This table or related slice is empty.
      1. VTUpMod
    1. COWhy do you want to merge them at all?
      singulars
      1. PORecommended approach to merging two tables
      1. USAakashM
    2. COQuerying, reporting and maintenance. Those two tables have exactly the same columns except the FK, we now have two LINQ to SQL entities, separate controllers and views for each and have to merge them when preparing data for reports, so it was suggested that those two tables are merged.
      singulars
      1. PORecommended approach to merging two tables
      1. USPawel Krakowiak
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload