数据库设计，实施问题
关于我正在从事的项目的 SQL 数据库设计的问题。
我将每隔几秒接收一次数据，并且需要将这些数据存储到数据库中。我的 DBMS 使用 mySQL。数据需要存储在数据库中，并且每条数据都附加一个用户ID。我将只处理每个应用程序的一个用户。因此，应用程序的每个实例将仅处理一个用户数据。不过，远程数据库将存储所有用户数据。所以，这就是为什么我需要用户 ID 来知道谁的数据是谁的。
我的想法是等到我收到大约 50 个数据包并创建所有 50 个数据包的分隔字符串。 （可能用逗号分隔）然后将该字符串与用户 ID 一起推送到数据库。并像这样存储数据。我的问题是，这是一个好方法吗？有更好的办法吗？这是不好的做法吗？请提示！ =)
我将收到大量此类数据。每秒一个数据包，有时甚至更快。请告诉我你的想法。
DBMS 将在远程计算机上运行。该应用程序将在 Android 手机上运行。
提前致谢！
Question regarding my sql database design for a project i am working on.
I will be receiving data every few seconds and i am going to need to store that data into a database. I am using mySQL for my DBMS. The data needs to be stored in the database with a userid attached to each piece of data. I will only be handling one user per application. So, each instance of the application will only be handling one users data. The remote database will be storing all users data though. So, that is why i need userid's to know whose data is whose.
My idea was to wait until i receive like 50 data packets and create a delimited string of all 50 data packets. (Maybe separated by commas) Then push that string to the database along with the userid. And store the data like that. My question is, is that a good way to do it? Is there a better way? Is this bad practice? TIPS PLEASE! =)
I will be receiving a lot of this data. One data packet like every second, sometimes faster. Just let me know what you think.
The DBMS will be running on a remote machine. The application will be running on an android phone.
Thanks in advance!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
我不建议将一堆值连接在一起以将分隔字符串发送到数据库。这只会在数据库上创建额外的工作来解析字符串。
任何与数据库交互的合理框架都将允许您创建并向数据库发送具有不同绑定变量值的批量 SQL 语句。这可以保持存储过程或 INSERT 语句的良好、友好的语法，使数据库保持正确的规范化，并实现最小化往返次数的性能目标。
I would not suggest concatenating a bunch of values together to send a delimited string to the database. That just creates additional work on the database to parse the string.
Any reasonable framework for interacting with the database will let you create and send batches of SQL statements with different values for the bind variables to the database. That keeps the nice, friendly syntax of the stored procedure or INSERT statement, it keeps the database properly normalized, and it accomplishes the performance goal of minimizing the number of round-trips.
如果 dbms 运行在一台性能良好的服务器上，并且您对数据所做的只是将数据插入到相当简单的表中，那么每秒 1 次插入根本不会造成压力。我预计它是难以衡量的。
If the dbms is running on a good server, and all you do with the data is a simple insert to a reasonably simple table, 1 insert per second should not a strain at all. I'd expect it to be hardly measurable.
您真正需要回答的问题是您对丢失数据的容忍度。每秒传输 1k 以下数据的请求并不多，尤其是使用 json 与 xml 时。话又说回来，在移动设备上，电池寿命是需要牢记的一点，因此每 5-60 秒发出一次请求也是可行的。
您没有理由不能批量更新服务器。
如果您不能容忍数据丢失，您可以在本地存储上收集 50 个更新批次，然后上传它们。如果传输失败，您可以重新发送。然而，在这种情况下，我希望有一些可以合理保证唯一的记录 ID，例如 UUID。这样，服务器就可以看到哪些记录已被处理，并将它们排除在重新处理之外。
The question you really have to answer is the tolerance you have for losing data. A request per second transferring under 1k of data isn't much, especially using json vs. xml. Then again, battery life is something to keep in mind on mobile devices, so making a request every 5-60 seconds is also doable.
There's no reason you cannot batch your updates to the server.
If you have no tolerance for data loss, you could collect your batch of 50 updates on local storage, and upload them. If a failure occurs in transmission you can resend. In this case, however, I would want to have some record ID that's reasonably guaranteed to be unique, such as a UUID. This way the server can see which records it's already processed and exclude them from reprocessing.
我将解决将其存储为分隔字符串的问题。存储后您打算如何查询这些数据？如果您需要查找一个或什至一小组值的数据，而不是整个字符串，请不要考虑以这种方式存储数据，因为它会给您带来糟糕的查询性能，并且编写查询会非常痛苦。一般来说，在一个字段中存储多条数据是一件坏事，这意味着你需要一个相关的表。
另外，对于您正在做的事情，如果您不需要对数据进行分析查询，那么 nosql 数据库可能是比关系数据库更好的选择。
I'm going to address the issue of storing it as a delimited string. HOw do you intend to query this data after it is stored? If you will need to find the data for one or aeven a small group of values but not the entire string, donot consider storing the data this way as it will give you horrible performance in querying and will be very painful to write queries for. In general, storing more than one piece of dat ina field is a bad thing, it means you need a related table.
Also, for what you are doing, if you don't need to to analytical querying of the data, perhaps a nosql database would be a better choice than a relational database.