MySQL 关系

2021-12-26 00:00:00 foreign-keys mysql database-design

我想弄清楚如何构建这个数据库.我之前用过苹果的核心数据就好了,我现在只是在做一个需要 MySQL 的不同项目.我对 MySQL 很陌生,所以请放轻松.:)

I am trying to figure out how to structure this database. I have used Apple's core data before just fine, I'm just working on a different project now that requires MySQL. I am very new to MySQL so please go easy on me. :)

在这个例子中,假设我有三个表,UserDeviceLocation.推导出来,一个Location可以有多个Device,而Device只能有一个Location;每个 User 都有它的主键,UserID,我需要使用它来获取正确的信息.

For this example, let's say I have three tables, User, Device, and Location. Drawing it out, a Location can have many Devices, but the Device can only have one Location; Each User has its primary key, UserID, of which I need to use to fetch the correct information.

那么我如何在这里建立这样的关系?我听说过创建索引和外键,但我不确定它们是如何工作的.

So how do I create a relationship like this here? I've heard of creating an index and a foreign key and I'm not sure how they work exactly.

最后,我需要做的是能够访问 User 的特定表并查看与该 User 关联的所有 Locations.我还需要能够在某个 Location 为某个 User 添加一个 Device.

In the end, what I need to do is be able to access the User's specific table and view all of the Locations associated with that User. I will also need to be able to add a Device at a certain Location for a certain User.

再一次,请原谅我,因为我正试图将我的头围绕在 MySQL 上.我正在使用 HeidiSQL 进行数据库编辑.

Again, please forgive me as I'm trying to wrap my head around MySQL. I am using HeidiSQL to do my database editing.

推荐答案

User - Device 是多对多关系,因此您需要引入一个中间表来解决该关系.该表仅由两个外键组成,一个引用用户表,一个引用设备.设备 - 可以使用设备表中指向位置表的简单外键来处理位置.

User - Device is a many-to-many relationship, so you'll want to introduce an intermediary table to resolve that relationship. That table simply consists of two foreign keys, one referencing the User table and one referencing Device. Device - Location can be handled with a simple foreign key in the Device table pointing to a Location table.

相关文章