CODEWITHSUNDEEP
databasedatabase-designentity-relationship
Smeckop
Smeckop

Reputation: 69

Best Solution - Ternary or Binary Relationship

I am trying to complete a question for one of my courses, and it revolves around which relationship is the best solution - binary or ternary.

Relationship examples

enter image description here

So it is a video store, with three entities - Video, Member, and Casual.

Members, and Casuals can borrow videos, and the date it was borrowed is recorded.

Would a ternary relationship be the most suitable solution, as there is no distinction between a member and a casual borrowing a video?

Upvotes: 1

Views: 1945

Answers (1)

philipxy
philipxy

Reputation: 15148

What ternary (3-entity) relationship do you consider the "Borrows" diamond to stand for? It's between a member client, a video, a casual client and a loan date. "MEMBER borrowed VIDEO from CASUAL on LOANDATE"? "VIDEO is of CASUAL punching MEMBER born on LOANDATE"? "MEMBER borrowed VIDEO due on LOANDATE and CASUAL is null or CASUAL borrowed VIDEO due on LOANDATE and MEMBER is null"? Ditto for each of the left & right binary "Borrows" relationships.

(I am using a predicate (statement template parameterized by columns) to express the "meaning" of a table. When you plug in a row you get a proposition (statement). Every table (base or query result) holds the rows that make its predicate into a true proposition. Relation operators (join, union, etc) are chosen to alternatively express, and to calculate, predicate expressions using logic non-terminals (AND, OR, etc) of a desired query predicate. )

The diagram has a diamond. It has to stand for a relationship between MEMBER, VIDEO and CASUAL entities plus LOANDATE. It does seem hard to come up with a sensible one for the ternary Borrows other than the AND or other combination of smaller ones, doesn't it? If you can't think of one, then that's not a diagram that describes your situations. I can't think of one that is limited to triplets from those three kinds of entities either. Whereas the binaries make sense easily.

PS My only caveat is: The third option I gave really is a typical relationship as embodied in database tables. But it isn't a relationship just on entities of the model. It also involves a "null" value. If we consider the relationships to be among values that identify entities or don't, then we can have that relationship that's using null.

Borrows:
    "MEMBER borrowed VIDEO due on LOANDATE and CASUAL is null
    or CASUAL borrowed VIDEO due on LOANDATE and MEMBER is null"
MEMBER  VIDEO   CASUAL   LOANDATE
=================================
  1       a      null    1/1/2000
  2       b      null    1/2/2000
 null     c    1234567   2/3/2000

I don't know what your teachers intended. But I suspect that they didn't expect you to involve null.

PPS Re "Would the binary relationship have a problem where a MEMBER and a CASUAL could BORROW the same VIDEO at the same time?" I'm not sure what you are thinking. But the relationship just contains the rows that make a true statement from its statement template. It describes the situation. It cannot cause the situation to be a certain way. The "business rules" (and physics!) limit the situations that arise. But you do need to record enough information to run the business. And the DBMS can be told about impossible database states so it can reject them.

Upvotes: 1

Related Questions