MySQL 数据库设计中“n:m”和“1:n”的含义

Meaning of "n:m" and "1:n" in database design

提问by Abdullah Khan

In database design what do n:mand 1:nmean?

在数据库设计中n:m和1:n是什么意思？

Does it have anything to do with keys or relationships?

它与密钥或关系有什么关系吗？

回答by Matti Virkkunen

m:nis used to denote a many-to-many relationship (mobjects on the other side related to non the other) while 1:nrefers to a one-to-many relationship (1object on the other side related to non the other).

m:n用于表示多对多关系（m另一侧的对象与另一侧相关n），而1:n指的是一对多关系（1另一侧的对象与另一侧相关n）。

回答by tdammers

1:n means 'one-to-many'; you have two tables, and each row of table A may be referenced by any number of rows in table B, but each row in table B can only reference one row in table A (or none at all).

1:n 表示“一对多”；你有两个表，表 A 的每一行都可以被表 B 中的任意数量的行引用，但是表 B 中的每一行只能引用表 A 中的一行（或根本没有）。

n:m (or n:n) means 'many-to-many'; each row in table A can reference many rows in table B, and each row in table B can reference many rows in table A.

n:m（或 n:n）表示“多对多”；A表的每一行可以引用B表的多行，B表的每一行可以引用A表的多行。

A 1:n relationship is typically modelled using a simple foreign key - one column in table A references a similar column in table B, typically the primary key. Since the primary key uniquely identifies exactly one row, this row can be referenced by many rows in table A, but each row in table A can only reference one row in table B.

1:n 关系通常使用简单的外键建模 - 表 A 中的一列引用表 B 中的类似列，通常是主键。由于主键只能唯一标识一行，因此该行可以被A表中的多行引用，但A表中的每一行只能引用B表中的一行。

A n:m relationship cannot be done this way; a common solution is to use a link table that contains two foreign key columns, one for each table it links. For each reference between table A and table B, one row is inserted into the link table, containing the IDs of the corresponding rows.

n:m 关系不能以这种方式完成；一个常见的解决方案是使用一个包含两个外键列的链接表，它链接的每个表一个。对于表 A 和表 B 之间的每个引用，将向链接表中插入一行，其中包含相应行的 ID。

回答by Ash

n:m --> if you dont know both n and m it is simply many to many and it is represented by a bridge table between 2 other tables like

n:m --> 如果你不知道 n 和 m，它只是多对多，它由 2 个其他表之间的桥表表示

   -- This table will hold our phone calls.
CREATE TABLE dbo.PhoneCalls
(
   ID INT IDENTITY(1, 1) NOT NULL,
   CallTime DATETIME NOT NULL DEFAULT GETDATE(),
   CallerPhoneNumber CHAR(10) NOT NULL
)

-- This table will hold our "tickets" (or cases).
CREATE TABLE dbo.Tickets
(
   ID INT IDENTITY(1, 1) NOT NULL,
   CreatedTime DATETIME NOT NULL DEFAULT GETDATE(),
   Subject VARCHAR(250) NOT NULL,
   Notes VARCHAR(8000) NOT NULL,
   Completed BIT NOT NULL DEFAULT 0
)

this is the bridge table for implementing Mapping between 2 tables

这是实现2个表之间映射的桥接表

CREATE TABLE dbo.PhoneCalls_Tickets
(
   PhoneCallID INT NOT NULL,
   TicketID INT NOT NULL
)

One to Many (1:n) is simply one table which has a column as primary key and another table which has this column as a foreign key relationship

一对多 (1:n) 只是一个表，其中一列作为主键，另一张表将此列作为外键关系

Kind of like Product and Product Category where one product Category can have Many products

有点像产品和产品类别，其中一个产品类别可以有许多产品

回答by nvogel

In a relational database all types of relationships are represented in the same way: as relations. The candidate key(s) of each relation (and possibly other constraints as well) determine what kind of relationship is being represented. 1:n and m:n are two kinds of binary relationship:

在关系数据库中，所有类型的关系都以相同的方式表示：作为关系。每个关系的候选键（以及可能的其他约束）决定了所表示的关系类型。1:n 和 m:n 是两种二元关系：

C {Employee*,Company}
B {Book*,Author*}

In each case * designates the key attribute(s). {Book,Author} is a compound key.

在每种情况下，* 表示关键属性。{Book,Author} 是一个复合键。

C is a relation where each employee works for only onecompany but each company may have manyemployees (1:n): B is a relation where a book can have manyauthors and an author may write manybooks (m:n):

C 是一种关系，每个员工只为一家公司工作，但每个公司可能有很多员工（1:n）：B 是一种关系，一本书可以有很多作者，作者可以写很多书（m:n）：

Notice that the key constraints ensure that each employee can only be associated with one company whereas any combination of books and authors is permitted.

请注意，关键约束确保每个员工只能与一家公司相关联，而允许书籍和作者的任意组合。

Other kinds of relationship are possible as well: n-ary (having more than two components); fixed cardinality (m:n where m and n are fixed constants or ranges); directional; and so on. William Kent in his book "Data and Reality" identifies at least 432 kinds - and that's just for binary relationships. In practice, the binary relationships 1:n and m:n are very common and are usually singled out as specially important in designing and understanding data models.

其他类型的关系也是可能的：n-ary（具有两个以上的组件）；固定基数（m:n，其中 m 和 n 是固定常数或范围）；定向；等等。威廉·肯特在他的《数据与现实》一书中确定了至少 432 种——这只是二元关系。在实践中，二元关系 1:n 和 m:n 非常常见，通常在设计和理解数据模型时特别重要。

回答by Paul Tomblin

To explain the two concepts by example, imagine you have an order entry system for a bookstore. The mapping of orders to items is many to many (n:m) because each order can have multiple items, and each item can be ordered by multiple orders. On the other hand, a lookup between customers and order is one to many (1:n) because a customer can place more than one order, but an order is never for more than one customer.

为了通过示例解释这两个概念，假设您有一个书店的订单输入系统。订单到商品的映射是多对多（n:m），因为每个订单可以有多个商品，每个商品可以由多个订单订购。另一方面，客户和订单之间的查找是一对多 (1:n)，因为一个客户可以下多个订单，但一个订单永远不会给多个客户。

回答by Adrian Regan

Many to Many (n:m) One to Many (1:n)

多对多 (n:m) 一对多 (1:n)

回答by Matovu Ronald

m:n refers to many to many relationship where as 1:n means one to many relationship forexample employee(id,name,skillset) skillset(id,skillname,qualifications)

m:n 表示多对多关系，其中 1:n 表示一对多关系，例如员工(id,name,skillset) Skillset(id,skillname,qualifications)

in this case the one employee can have many skills and ignoring other cases you can say that its a 1:N relationship

在这种情况下，一名员工可以拥有许多技能，而忽略其他情况，您可以说它是 1:N 关系

回答by unpluggeDloop

What does the letter 'N' on a relationship line in an Entity Relationship diagram mean? Any number

实体关系图中关系线上的字母“N”是什么意思？任何数字

M:N

男：女

M - ordinality - describes the minimum (ordinal vs mandatory)

M - 序数 - 描述最小值（序数 vs 强制）

N - cardinality - describes the miximum

N - 基数 - 描述混合

1:N(n=0,1,2,3...) one to zero or more

1:N(n=0,1,2,3...) 一到零或更多

M:N(m and n=0,1,2,3...) zero or more to zero or more (many to many)

M:N(m and n=0,1,2,3...) 零或更多到零或更多（多对多）

1:1one to one

1:1一对一

Find more here: https://www.smartdraw.com/entity-relationship-diagram/

在此处查找更多信息：https: //www.smartdraw.com/entity-relationship-diagram/

回答by Amin Shojaei

Imagine you have have a Bookmodel and a Pagemodel,

假设你有一个Book模型和一个Page模型，

1:N means:
One book can have **many** pages. One page can only be in **one** book.


N:N means:
One book can have **many** pages. And one page can be in **many** books.