12

I just started learning database and I'm confused with using one to many vs many to many relationship.

so I came up with a simple example on the relation beside customer and food. Customers orders food.

relationship

1)If i use one to many relationship, I will say that A customer can order MANY food.

onetomany

Base on the above diagram,

1)bob orders noodles and rice
2)jane orders noodles and crab

1)If i use many to many relationship, I will say that MANY customer can order MANY food. enter image description here

1)bob orders noodles and rice
2)jane orders noodles and crab

I keep seeing it as the same thing. 

bob still orders noodles and rice 
and 
jane still orders noodles and crab

regardless whether I use one to many or many to many relationships. correct me if I am wrong.

user2947249
  • 187
  • 2
  • 3
  • 11
  • In your food example, there is one logical mistake. Because in restaurants we don't make dishes. We make orders. And then order contains dishes. So There is will be obviously one-to-many connection from order to dish. And one-to-many connections from person to order (we can create as one order as many) And as for the book example, the dish itself may have many people that actually did the dish, analogy from co-authoring. – DevAnimal Jul 03 '20 at 09:48

3 Answers3

24

The food sample does work well for a one-to-many relationship: One customer can order many dishes.

The many-to-many relationship is better described by a book sample:

An author can write many books (that would be a one-to-many relationship). But he can have co-authors also involved - one book can have many authors.

Thorsten Dittmar
  • 55,956
  • 8
  • 91
  • 139
  • thank for your explaination. sorry but just to ask, for my food sample example, would there be anything wrong if I choose to use many to many relationships? or maybe should I say, in relational database design, there is no right and wrong, as long as it makes sense? – user2947249 Jan 28 '14 at 14:09
  • It really depends on what you want - do you need to know which food was ordered by which customers? Then an m2m might be a quicker solution (you can also achieve this in an o2m model, but you'd have to search over all the customers). If you only need to know what each customer ordered, an o2m model seems more appropriate. – Thorsten Dittmar Jan 28 '14 at 14:10
9

The one-to-many relationship diagram is probably misleading because noodles appears twice, while you should only have one record (like you correctly have in the many-to-many diagram).

Your example is a many-to-many relationship: a customer can order many food items, and the same food items can be ordered by many customers.

If you model it as a one-to-many relationship, you are either saying that the customer can order only one item, or that the item can be ordered by one customer only.

Don't confuse participation with cardinality: the term "one-to-many" says that an entity occurrence (a single record, e.g. noodles) on the "one" side of the relationship can occur only once, while an entity occurrence (e.g. Bob) on the "many" side can occur ... many times.

An example of one-to-many relationship, in the noodle-restaurant-chain scenario: an employee works for a branch (and one branch only), while the same branch will have many employees working there.

Marco Bonzanini
  • 756
  • 7
  • 11
3

Your Customer and Food example is many-to-many.
One Food type will be eaten by many Customer.
Similarly one Customer can eat multiple Food.

so, many to many means 1-n from both the sides.

Author and Book is the right example of one-to-many given that Book has no co-authors. Otherwise, it is also an example of many-to-many.

Yug Singh
  • 3,112
  • 5
  • 27
  • 52
Arun Raaj
  • 1,762
  • 1
  • 21
  • 20