数据库设计问题-字段或新表+ 一对多
我正在为活动管理网站设计一个数据库 - 有一个场地表和一个活动表。 每个事件都在一个场地（存储场地的 ID），并且每个场地都在一个城市中。 必须可以按城市搜索活动，城市应该是场馆表中的一个字段（这可能会由于拼写错误而导致城市重复），或者应该有一个城市表（每个城市都有一个 ID 和名称）并且连接城市和场馆的一对多表（cityid、venueid）？
我知道这是一个非常基本的问题，但我不确定额外的联接和额外的两个表是否值得。
预先感谢
[编辑] @tvanfosson：从多对多更改为一对多，因为每个场馆都与一个城市相关联。
I'm designing a database for an events management site - there is a table of venues and a table of events. Each event is in a venue (stores the venue's id) and each venue is in a city. It must be possible to search for event by city, should city be a field in the venues table (which could possibly result in duplicate cities due to misspellings) or should there be a table of cities (each with an id and a name) and a one-to-many table linking cities with venues (cityid, venueid)?
I know this is a pretty basic question, but I'm not really sure whether the extra join and extra two tables would be worth it or not.
Thanks in advance
[EDIT] @tvanfosson: Changed from many-to-many to one-to-many since each venue is associated with a single city.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（7）
使用单独的表——这样您就有了一个城市主列表来填充下拉列表和/或自动建议字段，并且您可以通过存储 id 而不是冗余的字符串来节省空间。 如果您有 100 万个场馆，但只有 1000 个城市，那么无论是在存储方面还是在查询速度方面，这都是显着的节省，因为您不必从磁盘读取太多内容，而这会降低性能。
您可能不仅应该指定一个城市，还应该指定州（以便您知道场地位于哪个斯普林菲尔德）。
Use a separate table -- that way you have a master list of cities to populate dropdowns and/or autosuggest fields, and you save space by storing ids instead of strings redundantly. If you have a million venues, and only a thousand cities, that's significant savings, both in terms of storage, and in terms of query speed -- since you won't have to read as much off disk, which is what kills performance.
You should probably specify more than just a city, but also the state (so that you know which Springfield the venue is located in).
我假设城市集是固定的，相对较小，并且不太可能更新（对于拼写，您始终可以添加新的城市）。 在这种情况下，提供从 XML 文件提供的下拉列表中选择城市并将所选值存储在数据库中的列中的功能。 由于可能出现错误输入，我会避免使用用户提供的输入。
如果您有一个更加层次化的结构，其中城市位于县中，而县位于州中，那么基于表的方法可能更合适，因为您可以在多个位置拥有具有相同名称的城市。 在这种情况下，我认为使用数据库查询比通过 XML 更容易管理级联下拉菜单。
注意：可能没有“正确”的答案，因为它很大程度上取决于您的情况。
I assume that the set of cities is fixed, relatively small, and not likely to be updated (for spelling, you could always add new ones). In this case provide the ability to choose cities from a dropdown that is fed from an XML file and store the selected value in the database in a column. I would avoid using user-supplied input due to the chances for incorrect input.
If you have a more hierarchical structure where cities are in counties, which are in states, then a table-based approach may be more appropriate since you could have cities with the same name in multiple locations. In this case I think cascading dropdowns are easier to manage using database queries than via XML.
Note: there is probably no "right" answer as it is highly dependent on your circumstances.
可能值得考虑使用带有城市列的地址表。
解决方案实际上取决于其他功能是什么，以及数据库将来是否会用于其他功能。
这也是一个主观选择，在我看来，拥有一个单独的城市表可能有点过于标准化。
It might be worth considering having an address table, with a city column.
The solution really depends on what the other functionality is, and whether the database will ever be used for other functions in the future.
It's also a subjective choice, in my opinion having a seperate city table is probably slightly too normalised.
将连接城市和场馆的多对多关系转换为与附加表（例如venue_city）的一对多关系，您可以在其中存储每个城市的所有场馆，然后链接表 event 和存储venue_city 的表venue_city id 进入表事件。
Translate many-to-many relations linking cities and venues to one-to-many relation with the additional table (something like venue_city) where you can store all the venues for each city and then link the tables event and the table venue_city storing the venue_city id into the table event.
您可能应该有一个城市表 - 即使您现在不需要它，您可能想要添加邮政编码，或者让城市位于州内。
You should probably have a table of cities - even if you don't need it now, you might want to add zip codes, or have cities be in states.
如果我对你的情况理解正确的话，城市表是必须的，包括州。 您可以根据需要添加新的城市/州（添加之前检查表中是否存在）。 它将更加高效，并避免重复但拼写错误的城市名称。
If I understand your situation correctly, a city table is a must, including the state. You can add new city/states as needed (check for existence in the table before adding). It will be much more efficient and avoid duplicate, but misspelled city names.
不要在场馆中存储城市名称。 相反，分配一个城市 ID 并将其存储在场地中。 在加入查找特定城市的事件之前，请解析城市名称中的城市 ID，并将其用作加入条件。 为了方便和提高性能，这可以在存储过程中实现。
Don't store the city names in the venues. Instead allocate a city-id and store that in the venue. Before you do your join to find events for a particular city resolve the city-id from the city name and use that as your join criteria. This can be implemented in a stored procedure for convenience and increased performance.