0

I am trying to understand better 1-1 vs 1-0..1 relationship.

Let's say I have a Car, SteeringWheel, and Sunroof

  • 1 Car must have 1 SteeringWheel and 1 SteeringWheel must belong to 1 Car; therefore this is 1:1 relationship
  • 1 Car can have 0 or 1 Sunroof and a Sanroof must belong to 1 Car; therefore this is 0..1:1 relationship.

But in terms of entities, I am not sure how to implement 0..1:1 relationship.

Below is 1:1 relationship btw Car and Sunroof but it should be 1:0..1.

How to make it 1:0..1 relationship?

-------------- ONE TO ONE --------------
CAR                         SUNROOF
---------------             -------------
CarId PK    -1----+         SunroofId
Model             |         Diameter
Year              +--0..1-  CarId FK, UNIQUE
cd491415
  • 823
  • 2
  • 14
  • 33
  • 3
    Your example is actually a 1:1 relationship. If you don't have a sunroof you simply don't have a row in the SUNROOF table. – Sean Lange Sep 26 '18 at 18:22
  • OK, so the above is applicable to both 1:1 and 1:0..1 then? – cd491415 Sep 26 '18 at 18:35
  • Of course. Why wouldn't it work for that? You simply do a left join from Car to Sunroof. Are you sure that there will never be a case of a second sunroof in a vehicle? What about a t-top? They have two distinct pieces of glass. Some might be interchangeable but others are not and are sold as the driver side or passenger side. – Sean Lange Sep 26 '18 at 18:37
  • Sean, the point is 1:0..1, not the Sunroof. Sunroof is just example but assume, whatever it is it is only 1:0..1 relationship. If you can think of better one fine but main question I have is how to make 1:0..1 relationship, that is all. Dont worry about Sunroof and how many may a car have. Assume, only one is possible or none in a car. Thanks – cd491415 Sep 26 '18 at 18:40
  • Apparently you missed the important part of my previous comment. This part. "Of course. Why wouldn't it work for that? You simply do a left join from Car to Sunroof." – Sean Lange Sep 26 '18 at 18:41
  • OK, so then my solution above for 1:1 also works for 1:0..1 is what you are saying – cd491415 Sep 26 '18 at 18:42
  • From a data perspective yes it works just fine for that. But if you are talking about entity framework then I am not any help. – Sean Lange Sep 26 '18 at 18:44
  • OK, thanks. Can you post as an answer so I can accept and thanks again – cd491415 Sep 26 '18 at 18:48

2 Answers2

1

From a data perspective your design supports this correctly. To achieve the relationship beyond 1:1 you would just use a left join from Car to Sunroof. That way if there is no row for sunroof you get NULL. But the unique constraint on CarId prevents more than a single sunroof for a given car.

Sean Lange
  • 33,028
  • 3
  • 25
  • 40
0

I found this link that I think applies.

Implementing one-to-zero-or-one relation in SQL Server

Essentially, it says all 1-1 relationships are really 0..1-1. In that case the implementation would be the same.

Eric Hutchins
  • 73
  • 11