1

Suppose I have the following relationship diagram:

Diagram

How would you describe the relationship determined by the red line?

Reading from left to right you would say each order has only one customer so it could be described as a "many to one" whereas from right to left each customer can have many orders so you could say it is "one to many".

Without direction it seems to me to be futile to try and discuss a relationship yet I rarely see people say, it is a one to many (from customer to order) or many to one (from order to customer).

So my question is why is this the case (is it just implied from context?) or does the primary key have some relation, as in we wouldn't really store an order_ID in the customer table (I am not a DBA but I don't think this make sense from a design point of view) whereas we would have to store a customer as a foreign key in the order table otherwise we wouldn't know who bought the stuff, so would this give precedence to reading from right to left and we say the red line is a one to many relationship?

Any clarification would be great!

1
  • 1
    A FK says subrows in the referencing table appear in the referenced table once. FKs are ubiqitously wrongly called "relationships" by methods & presentations that don't understand that in the relational & ER models tables represent relation(ship)s/associations. A FK is associated with a certain relationship--the projection of the referencing table on the FK columns & the columns of some key. – philipxy May 13 '20 at 0:35
2

A Foreign Key always refers to a Unique Index or Primary Key. So the "one" side is always the one where the related columns are a key.

2
  • What about many to many? Could you comment on that with regards to your answer, is it just then it doesn't matter as both sides are many? And can a many to many have a primary or foreign key? – rifyinif Apr 6 '20 at 19:04
  • 1
    Many-to-Many is implemented with a linking table and two seperate one-to-many relationships. – David Browne - Microsoft Apr 6 '20 at 19:45
0

In most cases the way you describe entities and their relationships in your model simply follows the natural order of things IRL. Orders don't suddenly appear by themselves and spring customer references. Customers come first, and each of them can have zero or more orders, to which they grant references. Going to the physical model, again, you must have a customer row before an order row for the referential integrity constraint to work.

1
  • So is this correct: - A primary key ensures a unique entry in the customers table (i.e. each row is a distinct customer) -Having a foreign key of customer_id in the orders table means you can't add a row to that table without it having both a order_id AND a customer_id (i.e. each order has to have a customer otherwise we cannot add to the order table)? – rifyinif Apr 6 '20 at 19:07
0

This is an excellent question, according to my experience, in ERD diagrams and relational databases direction is implied. In RDBMS you always define Many-To->One (trivial case One-To->One) relationships. The Many side of the relationship, a.k.a children, references the One side, a.k.a parent and you implement this with a Foreign Key constraint. Technically speaking you have to access an index, fetch the Primary Key record of the One side and then visit this record to get more information.

You cannot do this the other way around unless we are speaking about Object-Relational DBMS such as Postgres, Intersystems Cache, etc. These DBMS allow you to define a bi-directional relationship between the two entities (tables). In that case accessing records the other way around, i.e. One--To-->Many is achieved by using an array of references (children). In ORMs you have classes that reference each other the same way we described here.

WARNING: Most RDBMS in the IT market are NOT relational database management systems in the strict sense, think about null values, duplicate records etc, many of these allowed features break the definition of what a Relation is.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.