两个地点的 ERD
我正在设计一个数据库，但遇到了这个问题：
我的案例是设计一个 ERD 来跟踪汽车及其从一个位置到另一个位置的移动。
用户使用包含三个字段的表单：第一个字段用于输入汽车#。第二个字段用于输入汽车来自的位置（From_Location） 第三个字段是输入汽车要去的位置（To_Location）。
例如，Car#1 正在从位置#A 移动到位置#B。另一个示例，汽车#2 正在从位置#B 移动到位置#A。
如何创建位置表并将其连接到汽车表，以便它涵盖 From 和 To ？
该关系应该是多对多的。
我希望问题足够清楚。
I am desining a database and got stuck with this issue:
My case is to design an ERD to keep track of the cars and their movements from location to location.
The users utilize a form which contains three fields: The first one is used to enter the car#. The second field is used to enter the location the car came from (From_Location)
and the third field is to enter the location the car is going to (To_Location).
For instance, Car#1 is moving from location#A to location#B. Another example, car#2 is moving from location#B to Location#A.
How can I create the location table and connect it to the car table so that it covers From and To ?
The relationship should be many to many.
I hope the problem is clear enough.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
我将有三个表
Car 将具有适当的汽车信息
位置只是一个字段，除非您想添加纬度/经度信息等...
CarLocation 将包含至少四个字段
我可能会在 CarLocation 表中包含一个 UpdateTimestamp 字段。
I would have three tables
Car would have the appropriate car information
Location would be just the one field unless you wanted to add Lat/Long info etc...
CarLocation would include at least four fields
I would probably include an UpdateTimestamp field to the CarLocation table.
Car 和 Location 之间的关系是：对于每辆车来说，正好有两个位置（出发地和目的地），并且对于每个位置来说，有 0..n 辆车。
您应该：使用以下字段创建 Car 表：LocationFrom 和 LocationTo 每个字段都是具有 LocationId 和 LocationName 的 Location 表的外键（您只能拥有后者，这是有争议的）。
为什么不需要第三个表：连接不是多对多 - 它是两对多。
当您拥有真正的多对多关系时，表无法分辨一个记录中的哪条记录指向另一个记录中的哪条记录，因此需要第三个表（尝试一下......例如，如果您要保存所有每辆车的位置 - 这将是多对多的关系，因为现在一辆汽车与 0..n 个位置相关，而一个位置与 0..n 个汽车相关。现在您如何知道哪辆汽车记录。与哪个位置相关（你可以有 n 列，但这是不合理的，所以你需要另一个表））无论如何这里你在 car 中只有两列与位置“直接”相关。
已添加：
所以你还需要历史记录......在这种情况下，你确实需要一个额外的表。
我会在原来的解决方案中添加一列，以便现在表车具有列：
ID、发件人位置、收件人位置、LocationHistoryId
FromLocation 和 ToLocation 保持不变，LocationHistoryId 是 LocationHistory 表的外键，该表具有以下列：
汽车 ID、位置 ID
其中键是包含两个字段的复合键（显然 LocationId 指向位置表）
The relationship between Car and Location is: for every car there are exactly two locations (from and to), and for every location there are 0..n cars.
You should: make Car table with fields: LocationFrom and LocationTo each is a foreign key to table Location which has LocationId and LocationName (you can have only the latter, that's arguable).
Why you don't need third table: The connection isn't many to many - it's two to many.
When you have a true many to many relation the tables can't tell which record in one adresses which record on the other, and hence the need for a third table (try it out.. for example if a you were to save all of each car's locations - that would have been a many to many relation, because now a car is related to 0..n locations, and a location is related to 0..n cars. Now how would you be able to tell which car record is related to which location (you could have n columns for that, but that's unreasonable, so you need another table)) anyhow here you have only two columns in car which are related to location "directly".
Added:
So you need the history as well... In this case you really do need an extra table.
I would have add a column to my original solution so that now table car has columns:
Id, FromLocation, ToLocation, LocationHistoryId
FromLocation and ToLocation remains the same, LocationHistoryId is a foreign key of table LocationHistory which has the following columns:
CarId, LocationId
Where the key is a composite key that includes both fields (and obviously LocationId points to Location table)