数据库和应用程序设计 - 消除限制？
我正在开发一个在 Windows Mobile 上运行的 Compact Framework 应用程序。送货司机将使用它来告诉他们的下一份工作并跟踪支出等。我在移动设备上有一个 SQL CE 数据库，在服务器上有一个 SQL Server。在努力解决同步框架的主要性能和配置问题之后，我最终使用 WCF 编写了自己的同步代码。这运行良好，并且比同步框架快得多，但我被要求进一步加快速度。现在我们进入问题的细节。希望我能清楚地解释这一点。
同步一次只对一张表进行，并且是单向的。更新仅从服务器发送到 PDA。返回服务器的数据以完全不同的方式处理。首先，我删除 PDA 上已从服务器删除的所有记录。由于数据库限制，我必须先从“子”表中删除，然后再从“父”表中删除，因此我从底部开始处理层次结构。 EG 我先从发票表中删除记录，然后再从产品表中删除。
接下来，我将已添加到服务器上的新记录添加到 PDA 中。在这种情况下，我必须首先更新父表，然后逐步解决层次结构，然后更新子表。
问题是我的老板不喜欢这样的事实：当送货司机只需要 发票产品表。 InvoiceProduct 表将发票和产品表链接在一起，并包含有关产品的一些信息。我的意思是他们的数据库设计没有标准化，产品名称已被重复并存储在invoiceProduct表和product表中。当然，我们都知道这是糟糕的设计，但他们似乎这样做是为了提高这种情况下的性能。
显而易见的解决方案是从 PDA 数据库中完全删除产品表。但是我不能这样做，因为有时需要它。司机可以即时将新产品添加到发票中。我的老板建议他们可以偶尔同步大型产品表，或者当他们尝试添加产品但发现它不存在时。 这不适用于当前的设计，因为如果下载的发票包含 PDA 上没有的新产品，则会引发数据库外键错误。
很抱歉发布这么长的消息。希望这是有道理的。我不想删除我的数据库约束并弄乱我漂亮的数据结构:(
I'm working on a Compact Framework app running on Windows Mobile. It's to be used by delivery drivers to tell them their next job and track spending etc. I have a SQL CE database on the mobile devices and SQL Server on the server. After struggling with major performance and configuration problems with the Sync Framework I ended up writing my own sync code using WCF. This works well and is a lot faster than the Sync Framework but I've been asked to speed it up further. Now we get into the details of the problem. Hopefully I can explain this clearly.
The synchronisation works one table at a time and is only one-way. Updates are sent from the server to the PDA only. Data travelling back to the server is handled a completely different way. First of all I delete any records on the PDA that have been removed from the server. Because of database constraints I have to delete from 'child' tables before deleting from 'parent' tables so I work up the heirachy from the bottom. E.G. I delete records from the invoice table before deleting from the products table.
Next I add new records to the PDA that have been added on the server. In this case I have to update the parent tables first and work down the heirachy and update child tables later.
The problem is that my boss doesn't like the fact that my app will keep a large table like the products table synchronised with the server when the delivery driver only needs the
invoiceProduct table. The invoiceProduct table links the invoice and products table together and contains some information about the product. I mean that their database design is not normalised and the product name has been duplicated and stored in the invoiceProduct table as well as the product table. Of course we all know this is poor design but it seems they have done this to improve performance in this type of situations.
The obvious solution is to just remove the products table completely from the PDA database. However I can't do this because it is sometimes needed. Drivers have the ability to add a new product to an invoice on the fly. My boss suggests that they could just synchronise the large products table occasionally or when they try to add a product and find that it's not there.
This won't work with the current design bacause if an invoice is downloaded containing a new product that is not on the PDA it will throw a database foreign key error.
Sorry about posting such a large message. Hopefully it makes sense. I don't want to remove my database constraints and mess up my nice data structure :(
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
您似乎遇到了一些架构问题。我正在开发一款具有类似情况的产品。我有一个客户端-服务器应用程序，客户端加载了太多不需要的数据。
我们使用ADO.NET（数据集）来反映客户端数据库的内容。 Dataset 类就像内存中的 CE SQL Server。
我们的公司开始拥有更大的客户，而我们的架构速度不够快，无法处理所有数据。
过去，我们做了以下事情。这些都不是快速解决方案：
在客户端
数据集。
只要您将数据库约束保留在 SQL Server 上，就不应该出现数据完整性问题。然而，在您的 PDA 端，您将需要进行更多测试以确保您的应用程序正常运行。
当您已经拥有现有架构时，这不是一个容易解决的问题。希望这些建议对您有所帮助。
You seems to be running into some architecture problem. I work on a product that somewhat has a similar situation. I had a client-server application where the client loaded too much data that isn't needed.
We used ADO.NET (Dataset) to reflect what the database has on the client side. The Dataset class is like a in memory CE SQL Server.
Our company starts having bigger clients and our architecture isn't fast enough to handle all the data.
In the past, we did the following. These are no fast solution:
on the client side
dataset.
As long as you keep your database constraint on the SQL Server, you should have no data integrity issue. However, on your PDA side, you will need to more testing to ensure your application runs properly.
This isn't an easy problem to solve when you already have an existing architecture. Hopefully these suggestions help you.
为您的产品添加一个created_on字段，并跟踪每个pda上次同步的时间。下载发票后，检查产品是否比上次同步更新，以及是否重新同步 PDA。看起来它不会把数据库搞砸太多？
Add a created_on field for your products and keep track of when the last time each pda synced. When the invoice is downloaded, check if the product is newer than the last sync and if its re-sync the pda. Does not seem like it would screw up the DB too much?