mysql数据库结构-数据仅与每个用户相关，因此用户表？
我想为卡车司机建立一个在线日志。目标是卡车司机登录后，他/她立即看到他/她今年/月/日的驾驶总数的快照，以及每年/月/日的一些其他总数。 因此数据库中存储的信息仅与每个用户（卡车司机）相关。我个人不需要整个数据库的任何统计数据（仅针对每个用户）。
假设有 10,000 个用户。
我的问题涉及 mySQL 数据库的设计。
由于存储的信息仅与每个用户相关，而不是大量，因此将数据存储在每个用户的表中是否有意义……最多 10,000 个表？这会产生最高效/最快的数据库吗？ 或者我应该将所有行转储到一个大的“日志”表中，并将其与另一个表“用户”相关......即使分析只会针对每个用户进行？
以下是每个用户需要存储的一些信息（最多大约 30 列）： 日期 - 卡车品牌/型号 - 卡车 ID - 路线编号 - 从 - 到 - 总时间 - 停靠点 - 汽油消耗量 - 夜间 - 工作人员（第二名驾驶员） - ......
I would like to build an online logbook for truck drivers. The goal is that after a truck driver logs in, he/she immediately sees a snapshot of his/her driving total this year/month/day, together with some other totals also per year/month/day. So the information stored in the database is only relevant per user (truck driver). I personally don't require any statistical data out of the database as a whole (only per user).
Let's assume 10,000 users.
My question relates to the design of the mySQL database.
Since the information stored is only relevant per user and not in mass, does it makes sense to store the data in a table per user... leading up to 10,000 tables? Would that result in the most efficient/fastest database? OR should I dump all rows in one big 'Log' table, and have it relate to another table 'Users'.... even if analysis will only be done per user?
Here's some of the information that needs to be stored per user (ends up to about 30 columns):
Date - Truck make/model - Truck ID - Route # - From - To - Total time - Stops - Gas consumption - Night time - Crew (2nd driver) - ......
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
此处的简化示例
没有更多详细信息，这就是我的滚动方式。
Simplified example here
Without anymore details to go on this is how I'd roll it.
我建议使用单独的桌子，但也许在你的情况下，一张大桌子是一个不错的计划。
假设您编写了高效的 MySQL 来访问数据，那么对于大型数据集（例如您所描述的数据集）就不会有问题。
我会看一下 MySQL /Rails 性能：一张表，多行与多表，更少行？ 了解有关为什么选择表根可能是一个好主意的更多信息。另外哪个更有效：多个MySQL表或一张大表？包含有关该主题的一些有用信息。
I would suggest going for separate tables, but maybe in your case 1 large table is a good plan.
Assuming you write efficient MySQL to access the data you shouldn't have a problem with a large dataset such as the one you have described.
I'd take a look at MySQL / Rails Performance: One table, many rows vs. many tables, less rows? for more information on why going for the tables root may be a good idea. Also Which is more efficient: Multiple MySQL tables or one large table? contains some useful information on the subject.
您正在描述一个多租户数据库。 SO 有一个标签；我给你加了。
MSDN 有一篇不错的文章，为您提供了多租户数据库中涉及的问题的概述< /a>.结构范围从不共享任何内容到共享所有内容。请仔细注意，在“共享一切”结构中，数据库所有者（可能是您）很容易编写一个查询来打破用户隔离并将一个用户的数据公开给其他用户。
You're describing a multi-tenant database. SO has a tag for that; I added it for you.
MSDN has a decent article giving you an overview of the issues involved in multi-tenant databases. The structures range from shared nothing to shared everything. Note carefully that in a "shared everything" structure, it's fairly easy for the database owner (probably you) to write a query that breaks user isolation and exposes one user's data to other users.