Why would I need a third table in a one-to-many relationship

database-designmany-to-many

Good day,

Our db professor at the univeristy always said that one-to-many relationships with (0, M) in one side MUST have a third table to relate them. I did not ask him back then and now I cannot, but I would like to know why would he claim that? (specially the must part).

I am modeling a simple sensor-measure-campaign database and I am getting really puzzled with it, what do you think about my model, would it work as I expect? This relates to my question because I am doing something completely different to what I was taught, and I am afraid of building a broken model.

sensor-measure-campaign Model

Sensor has 0 or M measures, a measure belongs exactly to one sensor.
Campaign has 0 or M sensors, a sensor may be in 0 or N campaigns.
Campaign has 0 or M measures, a measure belongs exactly to one campaign.

Using my professsor's approach, I get 6 tables (an intermediate table for each pair). I see no need for 2 of those tables, but understanding that is the purpose of this question.

Ignoring him, I got Campaign and Sensor related by both Measure and another table (third table from many-to-many, which I called Instrument). I think it looks wrong to have such a double relationship, noticing that both Measure and Instrument will have FK to Campaign and Sensor (Instrument would be like an empty measure, IMO), I decided to simply do what I did.

I potentially need to query any combination (sensors/measures for a given campaign and measures for a given sensor), and I think I can easily (?) do that (using subqueries when measure is involved). I also want that if I delete a campaign or a sensor, its measures get removed, I can easily achieve that with an on delete cascade. What would be the drawbacks?

Sorry for the newbie question, any help will be appreciated. I already googled and found nothing, maybe I used the wrong terms, I am not pretending to spam here. At least a smarter query to Google Search will be appreciated, thanks.

Best Answer

I don't know what your professor has really said I am not sure if I understand your model. But maybe the following is interesting.

In the article A Logical Design Methodology for Relational Databases Using the Extended Entity-Relationship Model by T.J Teory, D. Yang and J.P. Fry is described how entity relationship diagrams can be transformed to relations (and tables). In Fig.8f (on the p 13 of the pdf) there is the following example for an 1:n relationship:

Each engineer can have at most one secretary. One secretary could work for several engineers.

There is a relationship "works-for" between the entities "secreatry" and "engineer".

  • A "secretary" "works-for" an "engineer".
  • Each "engineer" has at most one "secretary" that "works-for" her. It may be that an "engineer" has not "secretary" that "works-for" her.
  • A "secretary" can "work-for" one, more or no "engeneer"

In the article the authors propose to represent this as

ENGINEER(*EMP-NO,...,SEC-EMP-NO)
SECRETAY(*EMP-NO,...)

the fields marked by * are keys. The field SEC-EMP-NO referenes a EMP-NO from SECRETARY and is nullabel.

But there are authors that have the opinon that null values should not be allowed in relations. If one wants to avoid null values one needs a third relation to create the following model:

ENGINEER(*EMP-NO,...)
SECRETAY(*EMP-NO,...)
WORKS-FOR(*ENG-EMP-NO,SEC-EMP-NO)

ENG-EMP-NO references an EMP-NO in ENGINEER and SEC-EMP-NO references an EMP-NO in SECRETARY.

If an "engineer" always has a "secretary" that "works-for" her then the first representation with two relations does not contain null values.

Edit: I now found the comment in your post that links to the diagrams (http://i.imgur.com/gIG2D3h.jpg) of your professor: Actually he introduces a third table to avoid null values as described here.

Related Question