2 XML 导入 - 数据库设计
SQL Server 2008。
早上好，
如果有人能提供建议，我们将不胜感激。
目前我正在寻找导入两个 XML 文件：捐赠金额和捐赠制作者。
捐赠金额文件将由金额字段和 DonationMakerID 组成，捐赠制作者文件将由名称字段和 DonationMakerID 组成。捐赠者会将一定金额的捐赠带回金额文件中。
我的问题是，我偶尔会收到一个带有捐赠者 ID 的捐赠金额文件，该文件与数据库中当前的任何捐赠者无关，但可能与我当天晚些时候收到的捐赠者 XML 文件中的捐赠者 ID 相关。
现在，在我收到捐赠制作文件之前存储捐赠金额信息的最佳方式是什么？显然，在我有匹配的 Donation MakerID 之前，我无法加载捐款金额文件，但我宁愿在获得文件时加载该文件。
理想情况下，我需要一个 FK 约束，仅对 Donation Maker 表中已存在的 ID 强制 Donation Amount 表中的引用完整性...
我有一些想法，例如，DonationMakerID 和 XMLDonationMakerID 的 dbo.DonationAmount 中的两列，其中 DonationMakerID 可以允许 NULL 。然后，当我收到捐赠金额文件时，我将 DonationMakerID 加载到 XMLDonationMakerID 字段中，如果制造商表中存在匹配的 ID，我会将其加载到捐赠makerID 列中，否则我将其保留为 NULL。然后，当我收到后续的 DonationMaker XML 文件时，我可以根据 Donationmaker XML 文件中的 ID 检查 XMLDonationMakerID 字段，以及是否找到与更新 DonationMakerID 匹配的字段。
希望这一切都有道理。
非常感谢
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
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
首先，您可以等到收到第二个文件并按正确的顺序处理它们吗？或者你可以让他们同时发送或先发送捐赠者文件吗？解决时间问题是第一个也是最好的选择。
如果这是不可能的，请将坏记录移到单独的表中，以便可以维护外键约束（不要摆脱约束非常重要。）
然后在第二次导入后检查该表，并在正确的捐赠者进入后添加任何记录。无论您做什么，您都不希望将数据放入生产表中，直到关联的数据与之相关，否则您的数据库中将出现无意义的垃圾。数据库。为此，消除必要的 FK 约束可以保证将来出现其他数据完整性问题。
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.
如果是这样，您是否可以
类似的东西应该可以工作（我希望！）并允许您从 XML 插入数据。
Could you possibly
Something like that should work (I hope!) and allow you to insert your data from the XMLs.