Meaning of "n:m" and "1:n" in database design
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).
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.
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