[mysql] Meaning of "n:m" and "1:n" in database design

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

Does it have anything to do with keys or relationships?

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)

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

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:

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

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

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

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.

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

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.

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.

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.

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

M:N

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

N - cardinality - describes the miximum

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

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

1:1 one to one

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

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.

Imagine you have have a Book model and a Page model,

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.

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

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

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

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