我应该将联系信息放在单独的表中吗？
我正在规划一个数据库，其中有几个表，其中包含大量地址信息、城市、邮政编码、电子邮件地址、电话号码、传真号码等（大约 11 列），一个表就是一个组织表包含（最多）2 个地址（合法联系人和实际应该使用的联系人），此外每个用户都有与其相关的相同信息。
我们还必须在这些地址上运行一些地理定位功能（例如距离另一个地址 X 公里以内的每个地址）。
我有很多选项，每个选项都有自己的问题：
- 我可以将所有信息放入每个表中，但这将使表具有大量列，我在索引时会遇到问题，并且如果我更改地址格式需要一段时间才能修复它。
- 我可以将所有信息放入一个数组中并将其序列化，然后将序列化信息存储在一个字段中，与前一种方法有同样的问题，但列少一点，通过 mysql 查询的可用性也低得多
我可以使用以下命令创建一个单独的表地址信息并通过
将其链接到其他表- 将 address_id 列放入用户和组织表中
- 将 related_id 和 related_table 列放入地址表中
这应该使内容更加整洁，但它可能会因过度连接或其他原因而产生一些不可预见的问题。
我个人认为解决方案3.2是最好的，但我对此不太有信心，所以我征求意见。
I'm planning a database who has a couple of tables who contain plenty of address information, city, zip code, email address, phone #, fax #, and so on (about 11 columns worth of it), a table is an organizations table containing (up to) 2 addresses (legal contacts and contacts they should actually be used), plus every user has the same information tied to him.
We are going to have to run some geolocation stuff on those addresses too (like every address that's within X Kilometers from another address).
I have a bunch of options, each with its own problem:
- I could put all the information inside every table but that would make for tables with a very large amount of columns which I'd have problems indexing, and if I change my address format it'll take a while to fix it.
- I could put all the information inside an array and serialize it, then store the serialized information in one field, same problem with the previous method with a little less columns and much less availability through mysql queries
I could create a separate table with address information and link it to the other tables either by
- putting an address_id column in the users and organizations table
- putting a related_id and related_table columns in the addresses table
That should keep stuff tidier, but it might create some unforeseen problems with excessive joining or whatever.
Personally I think that solution 3.2 is the best, but I'm not too confident about it, so I'm asking for opinions.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
选项 2 肯定是不行的，因为它将过滤逻辑放入代码中，而不是让 DBMS 处理它们。
选项 1 或 3 将取决于您的需要。
如果你需要快速访问所有数据，并且通常会访问两个地址以及组织信息，那么你可能会考虑选项1。但是如果mysql中的表变得太大，这将使查询变得困难（即慢） 。
如果您正确索引表，选项 3 就很好。
Option 2 is definitely out as it would put the filtering logic into your codes instead of letting the DBMS handle them.
Option 1 or 3 will depend on your need.
if you need fast access to all the data, and you usually access both addresses along with the organization information, then you might consider option 1. But this will make it difficult to query out (i.e. slow) if the table get too big in mysql.
option 3 is good provided you index the tables correctly.