如何设置 MySQL 表以随时间推移跟踪变量？
假设我的网站有几个注册用户。
用户保存在单个表“users”中，该表为每个用户分配一个唯一的 ID。
我想让我的用户跟踪他们的费用、行驶里程、温度等。
我无法确定每个用户在登录时总是会输入所有可跟踪变量的值 - 例如可能发生的情况是：
'example data'
user date amount miles temp etc
1 3/1/2010 $10.00 5 54
2 3/1/2010 $20.00 15
1 3/12/2010 5 55
1 3/15/2010 $10.00 25 51
3 3/20/2010 45
3 4/12/2010 $20.00 10 54
针对这种情况设置桌子的最佳方法是什么？
我应该在每个用户注册时创建一个专属于他们的表吗？ （最终可能会出现数千个用户专用表）
'user-1 table'
date amount miles temp etc
3/1/2010 $10.00 5 54
3/12/2010 5 55
3/15/2010 $10.00 25 51
'user-3 table'
date amount miles temp etc
3/20/2010 45
4/12/2010 $20.00 10 54
and so on...
我是否应该创建一个与上面的示例数据基本相同的单个表？ （最终可能会得到一个巨大的表，需要对其进行梳理以查找具有请求的用户 ID 的行）。
'user data table'
user date amount miles temp etc
1 3/1/2010 $10.00 5 54
2 3/1/2010 $20.00 15
1 3/12/2010 5 55
1 3/15/2010 $10.00 25 51
3 3/20/2010 45
3 4/12/2010 $20.00 10 54
有什么建议吗？
Say I have several registered users in my website.
Users are saved on a single table 'users' that assigns a unique id for each one of them.
I want to allow my users to track their expenses, miles driven, temperature, etc.
I can't be sure each user will always enter a value for all trackable variables when they login -- so an example of what could happen would be:
'example data'
user date amount miles temp etc
1 3/1/2010 $10.00 5 54
2 3/1/2010 $20.00 15
1 3/12/2010 5 55
1 3/15/2010 $10.00 25 51
3 3/20/2010 45
3 4/12/2010 $20.00 10 54
What is the best way to set up my tables for this situation?
Should I create a table exclusive to each user when they register? (could end up with thousands of user-exclusive tables)
'user-1 table'
date amount miles temp etc
3/1/2010 $10.00 5 54
3/12/2010 5 55
3/15/2010 $10.00 25 51
'user-3 table'
date amount miles temp etc
3/20/2010 45
4/12/2010 $20.00 10 54
and so on...
Should I create a single table that is essentially the same as the example data above? (could end up with a gigantic table that needs to be combed to find rows with requested user id's).
'user data table'
user date amount miles temp etc
1 3/1/2010 $10.00 5 54
2 3/1/2010 $20.00 15
1 3/12/2010 5 55
1 3/15/2010 $10.00 25 51
3 3/20/2010 45
3 4/12/2010 $20.00 10 54
Any suggestions?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
数据库的构建是为了将相似的数据作为一个集合来处理。
您想要的是单个用户数据表，同一个表中的多个用户按 user_id 分割。不过，您可能希望进一步规范化，以便它存储：
或者更进一步，如果用户+日期进行特定旅行
但是，如果您总是（或几乎总是）在表单中显示数据输入时，数据基于所有输入列（如电子表格），那么为了简洁性、可编程性和性能，您最好坚持使用非标准化形式。
假设您正确且明智地使用索引，现代 RDBMS 旨在处理大量数据。索引允许查询仅查找所需的数据，因此将所有数据保存在一张表中通常不会造成什么损失。
Databases are built to handle similar data as a set together.
What you want is a single user-data-table, with multiple users in the same table split by user_id. You might want to further normalize that though, so that it stores:
Or even further if the user+date makes a specific trip
However, if you will always (or almost always) show your data in the form as entered, with the data pivoted on all the input columns (like a spreadsheet), then you would be better off sticking to the un-normalised form for brevity, programmability and performance.
Assuming you employ indexes properly and judiciously, modern RDBMS are built to handle gigantic amounts of data. The indexes allow the queries to seek only the data it needs, so there is normally little penalty in keeping it all in one table.
不，只需创建一张包含所有可能的可空字段的表。如果用户没有填写该参数 - 那么只需保留
NULL值即可。，并且如果您为
user_id字段指定索引（对于诸如 <代码>WHERE user_id = 42）No, just create one table with all possible nullable fields. If user hasn't filled that parameter - then just keep
NULLvalue there.Yes, and the query will be fast enough if you'll specify an index for
user_idfield (for queries likeWHERE user_id = 42)