关于多个一对多关系和外键的数据库设计问题
在我的数据库中有四个数据库：教师、学生、家长和地址。
教师、学生、家长可以拥有任意数量的地址（零个或多个）。
我想使用外键在教师和他们的地址之间（以及学生和他们的地址等）之间进行链接。
由于地址中的记录可以源自教师、学生或家长，因此我无法在地址中使用（单个）外键。由于教师等可以有任意数量的地址，因此我不能在教师记录中使用无限数量的外键。
这里什么是好的策略？
我正在考虑使用中间表，但这似乎有点麻烦。另外，让每个地址记录包含 3 个（可为 NULL）外键并且只使用一个外键是一个好主意吗？
In my database there are four databases: Teachers, Students, Parents and Addresses.
Teachers, students, parents can have any number of addresses (zero or more).
I would like to use foreign keys to link between teachers and their addresses (and between students and their addresses etc).
Since records in Addresses can originate in either a teacher, a student or a parent, I cannot use a (single) foreign key in Addresses. And since Teachers etc can have any number of addresses, I cannot use an unlimited number of foreign keys in a Teacher record.
What would be a good strategy here?
I was thinking of using intermediate tables but that seems a bit cumbersome. Also, is it a good idea to have each Address record include 3 (NULLable) foreign keys, and only use one?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
过去对我有用的东西是拥有 context 和 context_id 字段。 Context 将是父记录（例如 Teacher），而 context_id 将是其 id。因此，您将拥有类似的内容，
通过适当索引的表和健康的 memcached 服务，我们在 Cork'd 上看到了很好的结果。
Something that has worked for me in the past is having a context and context_id field. Context would be the parent record (e.g. Teacher), and context_id would be its id. So, you'd have something like
With appropriately indexed tables and a healthy serving of memcached we're seeing great results on Cork'd.
我会将其重新设计（取决于您提到的 4 个表的结构）到其中一个具有类型列（教师/学生/家长）的表（人员）中，从而使添加 person_id 作为地址
If 的外键变得更加简单由于要求非常不同，您需要为人员保留当前的 3 个表，我建议在地址上使用 3 个字段唯一键：
address_id：序列（表宽或每个 person_type/persond_id）
person_id：每个 person_type 表中的唯一 ID
person_type：值 T、S、P（根据教师/学生/家长的来源表而变化）
I would redesign this (depending on the structure of the 4 tables you mentioned) into either 1 table (People) which has a type column (Teacher/Student/Parent) making it much simpler to then add a person_id as foreign key to Addresses
If you need to keep the current 3 tables for people because of very different requirements I would advise having a 3 field unique key on Addresses:
address_id: sequence (either table wide or per person_type/persond_id)
person_id: unique id from each person_type table
person_type: values T,S,P (varies depending on table of origin Teacher/Student/Parent)
为每个基表添加一个连接表。教师地址、学生地址、家长地址。
Add a join table for each base table. TeacherAddresses, StudentAddresses, ParentAddresses.
使用表格来表示关系可能是这里的方法。您将需要一个 Parent_Address、Student_Address 和 Teacher_Address 表。这将使以后的查询变得更加简单。
Using the table to represent the relationship is probably the way to go here. You will need a Parent_Address, Student_Address and Teacher_Address table. It will make the querying that much simpler later on.
你所拥有的是多对多的关系。因此您需要一个连接表。
您可以在其中定义具有学生 ID 和地址 ID 的学生地址表、具有教师 ID 和地址 ID 的教师地址表等...
What you have is a many to many relationship. Therefore you need a join tables.
Where you define Students to Address table that has Student ID and Address ID, a Teacher to Address table that has Teacher ID and a Address ID, etc...