title: 2 XML Imports - Database design
body: <p>SQL Server 2008.</p>
  
  <p>Morning, </p>
  
  <p>If someone could offer their advice it would be very much appreciated. </p>
  
  <p>Currently I’m looking to import two XML files, Donation amount and Donation Maker.</p>
  
  <p>The Donation Amount file would comprise of an Amount field and a DonationMakerID, and the Donation Maker file would comprise of a Name field and DonationMakerID. The Donation Maker would make the donation of a certain amount brought back in the Amount file. </p>
  
  <p>My issue is I will occasionally receive a Donation Amount file with a Donation Maker ID that doesn’t relate to any Donation makers currently in the database but will probably relate to a Donation MakerID in the Donation Maker XML file which I will receive later that day. </p>
  
  <p>Now what would be the best way to store the Donation Amount information until I receive the Donation Maker file? I could obviously not load the Donation amount file until I have a matching Donation MakerID but I would rather load the file as and when I get them. </p>
  
  <p>Ideally I need a FK constraint that only enforces referential integrity in the Donation Amount table for IDs that already exist in the Donation Maker table… </p>
  
  <p>I have a few ideas for example, two columns in dbo.DonationAmount of DonationMakerID and XMLDonationMakerID where DonationMakerID can allow NULLs. Then when I receive a Donation Amount file I load the DonationMakerID into the XMLDonationMakerID field and if there is a matching ID in the maker table I load that to the Donation makerID column otherwise I leave it NULL.  Then when I receive subsequent Donation Maker XML files I can check the XMLDonationMakerID field against the ID in the Donationmaker XML file and if I find one that matches update  DonationMakerID. </p>
  
  <p>Hopefully that all makes sense. </p>
  
  <p>Many thanks</p>