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

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.

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

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.