CODEWITHSUNDEEP
databasedatabase-design
Álvaro García
Álvaro García

Reputation: 19386

which is the best option, ternary relation or two binary?

I simple example can be this. I have the Movies table, and the movies has directors and actors. In my database a movie can have one or more directors and one or more actors. Also I have a table Persons that has the information of the persons. So a person can have different roles in a movie, I have other table that has the posible roles, the table Roles.

In many cases I will be interested on to know the director of a movie, or the actors, so I can realtion the tables in two main ways.

First option: a ternary relation:

Movies(IDMovie, ...)
Persons(IDPerson, ...)
Roles(IDRol,...)
MoviesPersons(IDMovie, IDPerson, IDRol...)

In this case, I use a ternary relation.

The second option is:

MoviesDirectors(IDMovie, IDPerson,...)
MoviesActors(IDMovie, IDPerson,...)

In this case I can infer the rol from the relation table.

Which is the best option?

Thanks.

EDIT: If I use the option of two binary relations, if in the future, if I want to have the composer of the soundtrack, I need to create a new table and relations, however, with the ternary relation I don't need to do nothing, only add a new rol in the table Roles and anything else.

In performance is better two binary relations instead of one ternary relation?

Thanks.

Upvotes: 1

Views: 2211

Answers (1)

Branko Dimitrijevic
Branko Dimitrijevic

Reputation: 52137

Which is the best option?

You pretty much answered your own question - if you want the flexibility of adding new roles in the future without changing the structure of your database, the ternary relationship is the way to go.

I'd consider separate binary relationships if each of them needs to have different fields or constraints (which doesn't seem to be the case, from your description).

In performance is better two binary relations instead of one ternary relation?

Since the table representing the ternary relationship needs to physically store the role identifier (as opposed to binary relationships where role is inferred from the table name itself), your cache usage will be slightly worse.

However, by carefully ordering the fields in the composite PK, the ternary relationship can be made more suitable for certain kinds of queries. For example, the PK: {IDMovie, IDRol, IDPerson} can efficiently support the following queries:

  • Which people have worked on the given movie?(X)
  • Which people have worked on the given movie in the given role?

If you create an index on: {IDPerson, IDRol, IDMovie}, you'll also be able to efficiently query for:

  • Which movies has the given person worked on?(X)
  • Which movies has the given person worked on in the given role?

(X) With separate binary relationships, you'd need to query each of the junction tables. This is certainly not a problem with just two tables, but can become one as the number of tables grows (certainly from the maintenance and even from the performance point).

Upvotes: 1

Related Questions