Difference between one-to-many and many-to-one relationship

Question

What is the real difference between one-to-many and many-to-one relationship  It is only reversed  kind of   I can t find any  good-and-easy-to-understand  tutorial about this topic other than this one  SQL for Beginners  Part 3 - Database Relationships

User · Answer

Example  Two tables with one relation  SQL  In SQL  there is only one kind of relationship  it is called a Reference    Your front end may do helpful or confusing things  such as in some of the Answers   but that is a different story      A Foreign Key in one table  the referencing table  References a Primary Key in another table  the referenced table   In SQL terms  Bar references Foo Not the other way around  CREATE TABLE Foo       Foo   CHAR 10   NOT NULL  -- primary key     Name  CHAR 30   NOT NULL     CONSTRAINT PK             -- constraint name         PRIMARY KEY  Foo      -- pk         CREATE TABLE Bar       Bar   CHAR 10   NOT NULL  -- primary key     Foo   CHAR 10   NOT NULL  -- foreign key to Foo     Name  CHAR 30   NOT NULL     CONSTRAINT PK                -- constraint name         PRIMARY KEY  Bar         -- pk     CONSTRAINT Foo HasMany Bars  -- constraint name         FOREIGN KEY    Foo       -- fk in  this  referencing table         REFERENCES Foo Foo       -- pk in referenced table        Since Foo Foo is a Primary Key  it is unique  there is only one row for any given value of Foo   Since Bar Foo is a Reference  a Foreign Key  and there is no unique index on it  there can be many rows for any given value of Foo   Therefore the relation Foo  Bar is one-to-many   Now you can perceive  look at  the relation the other way around  Bar  Foo is many-to-one     But do not let that confuse you  for any one Bar row  there is just one Foo row that it References  In SQL  that is all we have  That is all that is necessary         What is the real difference between one to many and many to one relationship    There is only one relation  therefore there is no difference   Perception  from one  end  or the other  end   or reading it backwards  does not change the relation   Cardinality  Cardinality is declared first in the data model  which means Logical and Physical  the intent   and then in the implementation  the intent realised    Cardinality  One to zero-to-many In SQL that  the above  is all that is required   One to one-to-many You need a Transaction to enforce the one in the Referencing table   One to zero-to-one You need in Bar   CONSTRAINT AK    -- constraint name     UNIQUE  Foo  -- unique column  which makes it an Alternate Key   One to one You need a Transaction to enforce the one in the Referencing table   Many to many There is no such thing at the Physical level  recall  there is only one type of relation in SQL      At the early Logical levels during the modelling exercise  it is convenient to draw such a relation   Before the model gets close to implementation  it had better be elevated to using only things that can exist   Such a relation is resolved by implementing an Associative Table   Many-to-many Resolved

User · Answer

From this page about Database Terminology      Most relations between tables are one-to-many       Example          One area can be the habitat of many readers    One reader can have many subscriptions    One newspaper can have many subscriptions          A Many to One relation is the same as one-to-many  but from a different viewpoint          Many readers live in one area    Many subscriptions can be of one and the same reader    Many subscriptions are for one and the same newspaper

User · Answer

one-to-many has parent class contains n number of childrens so it is a collection mapping   many-to-one has n number of childrens contains one parent so it is a object mapping

User · Answer

There is no difference  It s just a matter of language and preference as to which way round you state the relationship

User · Answer

Yes  it a vice versa  It depends on which side of the relationship the entity is present on   For example  if one department can employ for several employees then  department to employee is a one to many relationship  1 department employs many employees   while employee to department relationship is many to one  many employees work in one department    More info on the relationship types   Database Relationships - IBM DB2 documentation

User · Answer

One-to-many and Many-to-one relationship is talking about the same logical relationship  eg an Owner may have many Homes  but a Home can only have one Owner  So in this example Owner is the One  and Homes are the Many  Each Home always has an owner id  eg the Foreign Key  as an extra column  The difference in implementation between these two  is which table defines the relationship  In One-to-Many  the Owner is where the relationship is defined  Eg  owner1 homes lists all the homes with owner1 s owner id In Many-to-One  the Home is where the relationship is defined  Eg  home1 owner lists owner1 s owner id  I dont actually know in what instance you would implement the many-to-one arrangement  because it seems a bit redundant as you already know the owner id  Perhaps its related to cleanness of deletions and changes

User · Answer

What is the real difference between one-to-many and many-to-one relationship    There are conceptual differences between these terms that should help you visualize the data and also possible differences in the generated schema that should be fully understood   Mostly the difference is one of perspective though   In a one-to-many relationship  the local table has one row that may be associated with many rows in another table   In the example from SQL for beginners  one Customer may be associated to many Orders   In the opposite many-to-one relationship  the local table may have many rows that are associated with one row in another table   In our example  many Orders may be associated to one Customer   This conceptual difference is important for mental representation   In addition  the schema which supports the relationship may be represented differently in the Customer and Order tables   For example  if the customer has columns id and name   id name 1 Bill Smith 2 Jim Kenshaw   Then for a Order to be associated with a Customer  many SQL implementations add to the Order table a column which stores the id of the associated Customer  in this schema customer id   id date amount customer id 10 20160620 12 34 1 11 20160620 7 58 1 12 20160621 158 01 2   In the above data rows  if we look at the customer id id column  we see that Bill Smith  customer-id  1  has 2 orders associated with him  one for  12 34 and one for  7 58   Jim Kenshaw  customer-id  2  has only 1 order for  158 01   What is important to realize is that typically the one-to-many relationship doesn t actually add any columns to the table that is the  one    The Customer has no extra columns which describe the relationship with Order   In fact the Customer might also have a one-to-many relationship with ShippingAddress and SalesCall tables and yet have no additional columns added to the Customer table   However  for a many-to-one relationship to be described  often an id column is added to the  many  table which is a foreign-key to the  one  table -- in this case a customer id column is added to the Order    To associated order  10 for  12 34 to Bill Smith  we assign the customer id column to Bill Smith s id 1   However  it is also possible for there to be another table that describes the Customer and Order relationship  so that no additional fields need to be added to the Order table   Instead of adding a customer id field to the Order table  there could be Customer Order table that contains keys for both the Customer and Order     customer id order id 1 10 1 11 2 12   In this case  the one-to-many and many-to-one is all conceptual since there are no schema changes between them   Which mechanism depends on your schema and SQL implementation   Hope this helps

User · Answer

---One to Many--- A Parent can have two or more children   ---Many to one--- Those 3 children can have a single Parent Both are similar  This can be used according to the need  If you want to find children for a particular parent  then you can go with One-To-Many   Or else  want to find parents for twins  you may go with Many-To-One

User · Answer

One-to-Many and Many-to-One are similar in Multiplicity but not Aspect  i e  Directionality     The mapping of Associations between entity classes and the Relationships between tables  There are two categories of Relationships    Multiplicity  ER term  cardinality    One-to-one relationships  Example Husband and Wife One-to-Many relationships  Example Mother and Children  Many-to-Many relationships  Example Student and Subject  Directionality   Not affect on mapping but makes difference on how we can access data       Uni-directional relationships   A relationship field or property that refers to the other entity  Bi-directional relationships  Each entity has a relationship field or property that refers to the other entity

User · Answer

Answer to your first question is   both are similar   Answer to your second question is  one-to-many --  a MAN MAN table  may have more than one wife WOMEN table  many-to-one --  more than one women have married one MAN   Now if you want to relate this relation with two tables MAN and WOMEN  one MAN table row may have many relations with rows in the WOMEN table  hope it clear

User · Answer

There s no practical difference  Just use the relationship which makes the most sense given the way you see your problem as Devendra illustrated

[sql] Difference between one-to-many and many-to-one relationship

Example

SQL

Cardinality

Examples related to sql

Examples related to relational-database

Examples related to one-to-many

Examples related to many-to-one