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

In database design what do n:m and 1:n mean?

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

Does it have anything to do with keys or relationships?

它与键或关系有关吗?

7 个解决方案

#1

m:n is used to denote a many-to-many relationship (m objects on the other side related to n on the other) while 1:n refers to a one-to-many relationship (1 object on the other side related to n on the other).

m:n表示多对多关系(另一边的m个对象与n有关)，1:n表示一对多关系(另一边的1个对象与n有关)。

#2

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意味着“一对多”;您有两个表，表B中的任何行都可以引用表A中的每一行，但是表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.

A n:m关系不能这么做;一个常见的解决方案是使用一个包含两个外键列的链接表，每个外键列对应它链接的每个表。对于表A和表B之间的每个引用，将一行插入到链接表中，其中包含相应行的id。

#3

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，那么它只是很多很多的表，它是由两个表之间的一个网桥表来表示的

   -- 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

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

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

有点像产品和产品类别一个产品类别可以有很多产品

#4

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.

在每种情况下*指定键属性(s)。{Book,Author}是复合键。

C is a relation where each employee works for only one company but each company may have many employees (1:n): B is a relation where a book can have many authors and an author may write many books (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元(包含两个以上的成分);固定基数(m:n，其中m和n为固定常数或范围);定向;等等。威廉·肯特在他的《数据与现实》一书中指出了至少432种类型——这仅仅是二元关系。在实践中，二进制关系1:n和m:n是非常常见的，并且通常在设计和理解数据模型时特别重要。

#5

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)，因为客户可以下多个订单，但是订单永远不会超过一个客户。

#6

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

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

#7

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是指许多关系，如:n是指一种关系，例如雇员(id、name、skillset)技能集(id、skillname、资格)

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的关系