mysql数据库规范化问题
这是我的 3 个表：
表 1 - 存储用户信息，它具有唯一的数据
表 2 - 存储地点类别，例如多伦多、纽约、伦敦等，因此这也是唯一的
表 3 - 具有重复信息。它存储用户去过的所有地方。
这 3 个表通过这些 id 链接或连接：
表 1 有一个“table1_id”
表 2 有一个“table2_id”和“place_name”
表 3 有一个“table3_id”、“table1_id”、“place_name”
我有一个接口，其中管理员可以看到所有用户。用户旁边是“编辑”按钮。单击该编辑按钮允许您在表单字段中编辑特定用户，该表单字段具有多个“地点”下拉框。
如果管理员编辑用户并为该用户添加 1 个“位置”，我会使用 php 插入该信息。如果管理员决定取消选择该 1 个“地点”，我是否将其删除或将其标记为打开和关闭？如果管理员决定为用户选择 2 个“位置”怎么样？更改第一个“地点”并添加额外的“地点”。我的表格会继续增长吗？我会得到多余的信息吗？
谢谢。
here is my 3 tables:
table 1 -- stores user information and it has unique data
table 2 -- stores place category such as, toronto, ny, london, etc hence this is is also unique
table 3 -- has duplicate information. it stores all the places a user have been.
the 3 tables are linked or joined by these ids:
table 1 has an "table1_id"
table 2 has an "table2_id" and "place_name"
table 3 has an "table3_id", "table1_id", "place_name"
i have an interface where an admin sees all users. beside a user is "edit" button. clicking on that edit button allows you to edit a specific user in a form fields which has a multiple drop down box for "places".
if an admin edits a user and add 1 "places" for the user, i insert that information using php. if the admin decides to deselect that 1 "places" do i delete it or mark it as on and off? how about if the admin decides to select 2 "places" for the user; change the first "places" and add an additional "places". will my table just keep growing and will i have just redundant information?
thanks.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
表 1 和表 2 听起来正确。表 3 不应包含 place_name。相反，您应该拥有对 table1_id 和 table2_id 的外键引用（您可以选择保留 table3_id）。这使得 table3 成为联结表。
当您想要删除用户的位置时，您将删除 table_3 中的一行（仅此而已）。添加地点时，首先检查它是否存在于表 1 中。如果不存在，则将其添加到表 1 中。然后，向表 3 中添加一行。更改（例如，为用户在 UI 中重命名地点）应该被分解为删除然后添加。
这将避免冗余信息。
Tables 1 and 2 sound correct. Table 3 should not contain place_name. Instead, you should have foreign key references to both table1_id and table2_id (you can optionally keep table3_id). This makes table3 a junction table.
When you want to delete a place for a user, you'll delete a row in table_3 (and that's it). When adding a place, you first check if it exists in table 1. If not, you add it to table 1. Then, you add a row to table 3. A change (e.g. renaming a place in the UI for a user) should be broken down into a delete then add.
This will avoid redundant information.
您如何处理这个问题取决于您的应用程序的需求。例如，您需要知道某个地点何时被删除吗？
最简单的方法是，每当为用户更新地点时，只需删除所有地点，然后重新添加选定的地点即可。
因此，如果取消选择所有位置并添加一个新位置，则结果将如下所示：
How you handle this depends on the needs of your app. E.g., do you need to know when a place was deleted?
The simplest way is that whenever the places are being update for a user, jsut delete all of them and then re-add the ones that are selected.
So, if all places were de-selected and a new one added, it would look like:
删除记录可能会影响并发插入 MySQL，因此使用开/关列而不是执行
DELETE可能是一种优化。您可以在多个列上设置
UNIQUE索引，确保任何给定的人员/位置组合仅存在一条记录。在重复密钥更新时插入... 语法可以简化这种情况下的查询，特别是在删除位置后重新启用位置时。通过此设置，您需要一些每天/每周/任何时间运行的维护脚本来
删除“关闭”记录，然后优化表。所以，这是一种方法。
Deleting records has the potential to affect concurrent inserts in MySQL, so having an on/off column rather than doing a
DELETEcan be an optimization down the road.You can set a
UNIQUEindex on multiple columns, ensuring that only one record exists for any given person/location combination. TheINSERT … ON DUPLICATE KEY UPDATEsyntax can simplify queries in this situation, particularly when a location is re-enabled after having been deleted.With this setup, you would want some maintenance script that runs every day/week/whatever to
DELETE"off" records and thenOPTIMIZE TABLE.So, that's one way to do it.