media.istockphoto.com
One to One Relationship (1:1)
When a row in a table is related to only one role in another table and vice versa,we say that is a one to one relationship. This relationship can be created using Primary key-Unique foreign key constraints.
For instance a Country can only have one UN Representative, and also a UN Representative can only represent one Country.
Let?s try out the implement and see for ourselves.
CREATE TABLE Country(Pk_Country_Id INT IDENTITY PRIMARY KEY,Name VARCHAR(100),Officiallang VARCHAR(100),Size INT(11),);CREATE TABLE UNrepresentative(Pk_UNrepresentative_Id INT PRIMARY KEY,Name VARCHAR(100),Gender VARCHAR(100),Fk_Country_Id INT UNIQUE FOREIGN KEY REFERENCES Country(Pk_Country_Id));INSERT INTO Country (‘Name’,’Officiallang’,?Size?)VALUES (‘Nigeria’,’English’,923,768);INSERT INTO Country (‘Name’,’Officiallang’,?Size?)VALUES (‘Ghana’,’English’,238,535);INSERT INTO Country (‘Name’,’Officiallang’,?Size?)VALUES (‘South Africa’,’English’,1,219,912);INSERT INTO UNrepresentative (‘Pk_Unrepresentative_Id’,’Name’,’Gender’,’Fk_Country_Id’)VALUES (51,’Abubakar Ahmad’,’Male’,1);INSERT INTO UNrepresentative (‘Pk_Unrepresentative_Id’,’Name’,’Gender’,’Fk_Country_Id’)VALUES (52,’Joseph Nkrumah’,’Male’,2);INSERT INTO UNrepresentative (‘Pk_Unrepresentative_Id’,’Name’,’Gender’,’Fk_Country_Id’)VALUES (53,’Lauren Zuma,’Female’,3);SELECT * FROM CountrySELECT * FROM UNrepresentative;
One-to-One (1:1) Relationship between Country-UNrepresentative Table
With Country(Pk_Country_Id) serving as the Primary key and UNrepresentative(fk_Country_id) as (Unique Key Constraint-Foreign Key), you can implement One-to-One Relationship.
One to Many Relationship (1:M)
This is where a row from one table can have multiple matching rows in another table this relationship is defined as a one to many relationship. This type of relationship can be created using Primary key-Foreign key relationship.
This kind of Relationship, allows a Car to have multiple Engineers.
Let?s try out the implementation and see for ourselves.
CREATE TABLE Car(Pk_Car_Id INT PRIMARY KEY,Brand VARCHAR(100),Model VARCHAR(100));CREATE TABLE Engineer(Pk_Engineer_Id INT PRIMARY KEY,FullName VARCHAR(100),MobileNo CHAR(11),Fk_Car_Id INT FOREIGN KEY REFERENCES Car(Pk_Car_Id));INSERT INTO Car (‘Brand’,’Model’)VALUES (‘Benz’,’GLK350′);INSERT INTO Car (‘Brand’,’Model’)VALUES (‘Toyota’,’Camry XLE’);INSERT INTO Engineer (‘Pk_Engineer_Id’,’FullName’,’MobileNo’,’Fk_Car_Id’)VALUES(50,’Elvis Young’,’08038888888′,2);INSERT INTO Engineer (‘Pk_Engineer_Id’,’FullName’,’MobileNo’,’Fk_Car_Id’)VALUES(51,’Bola Johnson’,’08020000000′,1);INSERT INTO Engineer (‘Pk_Engineer_Id’,’FullName’,’MobileNo’,’Fk_Car_Id’)VALUES(52,’Kalu Ikechi’,’09098888888′,1);INSERT INTO Engineer (‘Pk_Engineer_Id’,’FullName’,’MobileNo’,’Fk_Car_Id’)VALUES(53,’Smart Wonodu’,’08185555555′,1);INSERT INTO Engineer (‘Pk_Engineer_Id’,’FullName’,’MobileNo’,’Fk_Car_Id’)VALUES(54,Umaru Suleja’,’08056676666′,1);SELECT * FROM Car;SELECT * FROM Engineer;
One-to-Many (1:M) Relationship between Car-Engineer Table
Car(Pk_Car_Id) serving as the Primary Key and Engineer(Fk_Car_Id) as (Foreign Key).
Based on the Car (Pk_Car_Id)-Engineer(Fk_Car_Id) relationship, we now have a design for our database tables that consolidates the One-to-Many relationship using a foreign key!
Many to Many Relationship (M:M)
A row from one table can have multiple matching rows in another table, and a row in the other table can also have multiple matching rows in the first table this relationship is defined as a many to many relationship. This type of relationship can be created using a third table called ?Junction table? or ?Bridging table?. This Junction or Bridging table can be assumed as a place where attributes of the relationships between two lists of entities are stored.
This kind of Relationship, allows a junction or bridging table as a connection for the two tables.
Let?s try out the implementation and see for ourselves.
CREATE TABLE Student(StudentID INT(10) PRIMARY KEY,Name VARCHAR(100),);CREATE TABLE Class(ClassID INT(10) PRIMARY KEY,Course VARCHAR(100),);CREATE TABLE StudentClassRelation(StudentID INT(15) NOT NULL,ClassID INT(14) NOT NULL,FOREIGN KEY (StudentID) REFERENCES Student(StudentID),FOREIGN KEY (ClassID) REFERENCES Class(ClassID),UNIQUE (StudentID, ClassID));INSERT INTO Student (‘Name’)VALUES (‘Olu Alfonso’);INSERT INTO Student (‘Name’)VALUES (‘Amarachi Chinda’);INSERT INTO Class (‘Course’)VALUES (‘Biology’);INSERT INTO Class (‘Course’)VALUES (‘Chemistry’);INSERT INTO Class (‘Type’)VALUES (‘Physics’);INSERT INTO Class (‘Type’)VALUES (‘English’);INSERT INTO Class (‘Type’)VALUES (‘Computer Science’);INSERT INTO Class (‘Type’)VALUES (‘History’);INSERT INTO StudentClassRelation (‘StudentID’,’ClassID’)VALUES (1,2);INSERT INTO StudentClassRelation (‘StudentID’,’ClassID’)VALUES (1,4);INSERT INTO StudentClassRelation (‘StudentID’,’ClassID’)VALUES (1,6);INSERT INTO StudentClassRelation (‘StudentID’,’ClassID’)VALUES (2,3);INSERT INTO StudentClassRelation (‘StudentID’,’ClassID’)VALUES (2,1);INSERT INTO StudentClassRelation (‘StudentID’,’ClassID’)VALUES (2,6);INSERT INTO StudentClassRelation (‘StudentID’,’ClassID’)VALUES (2,1);
Many to Many (M:M) Relationship between Student-Class Table
StudentClassRelation Table
Two columns are stored in the Junction or Bridging Table one for each of the primary keys from the other tables, therefore combination of student and class records are stored here, while our student and class tables remains unaltered
This data structure looking this way, is easier to add more relationships between tables and also allow us update our students and Classes without influencing the relationships between them.
With our data structure in this way, it is easier to add more relationships between tables and to update our employees and departments without influencing the relationships between them.
