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 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.