2 XML Imports - Database design

Question

SQL Server 2008.

Morning,

If someone could offer their advice it would be very much appreciated.

Currently I’m looking to import two XML files, Donation amount and Donation Maker.

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.

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.

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.

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…

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.

Hopefully that all makes sense.

Many thanks

Answer 1

Could you possibly

• detect when a DomainMakerID is not yet present
• if so: add an new entry to your DomainMaker table, and flag it as "temporary" or "provisional" or whatever
• once you get the DomainMaker with that new ID, update the row and remove the "temporary" flag

Something like that should work (I hope!) and allow you to insert your data from the XMLs.

Answer 2

First, can you wait until you receive the second file and process them in the correct order? Or can you get them to send them at the same time or the donor file first? Fixing the timing issue is the first, best choice.

If that is not possible, move the bad records out to a separate table so you can maintain the foreign key constraints (It is really important to not get rid of the constraints.) Then check that table after the second import and add any records after the correct donor is in. Whatever you do, you do not want to put the data into the production table until the associated data is with it or you will have meaningless garbage in your database. And getting rid of a necessary FK constraint in order to do so is a guarantee of other problesm with data integrity in the future.