6

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.

8
  • Does Campaign actually have measures or does it only have measures via its Sensors? – Dave Jul 28 '15 at 18:23
  • @Dave I think they are actually related, I find no other way to query for measures in a campaign (relying on dates does not seem appropriate since a sensor may be used in many campaigns running in the same period of time). – la_jankong Jul 28 '15 at 18:32
  • The question you should ask (and the answer will clarify if your model needs 1 or 3 intermediate tables) is: "Can a Measure be related to a Sensor and a Campaign that are not related themselves?" If the answer is No, then your design is fine. If the answer is Yes, then the Measure needs to be related to Sensor and Campaign not through Instrument but through 2 other independent tables (or directly, it is not important if you have 2 tables there or 2 direct FKs). – ypercubeᵀᴹ Jul 28 '15 at 20:53
  • @ypercube about the second part of your reply, does it really make no difference? It was my main question, our professor claimed that (0,M)(0,1) and (0,M)(1,1) always needed a third table, anything else was wrong according to him. (If I am not clear enough, you may check the picture I posted in the comments of the first answer). – la_jankong Jul 28 '15 at 22:12
  • 3
    Either you misinterpreted what your professor said, or your professor misinformed you. – Walter Mitty Jul 29 '15 at 10:54
5

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.

1
  • 1
    Good point. The third table makes it possible to represent an optional relationship without introducing NULLS. – Walter Mitty Aug 4 '15 at 9:03
3

I want to distinguish carefully between analysis and design. You analyze the subject matter in order to "make sense" out of each of the values to be stored in the database. You design the database in order to come up with the containers (tables) and linkages (foreign keys) that make the database functional and hold the values you discovered during analysis.

I don't know your subject matter. I'm basing my understanding on your words:

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.

This is where I got confused.

Analysis A: There are three entities (sensors, measures, and campaigns) and two relationships (sensor-measure and measure-campaign). There is an indirect relationship between sensors and campaigns reflected by the fact that sometimes a sensor and a campaign will have one or more measures in common.

Analysis B: There are three entities (sensors, measures, and campaigns) and three relationships (sensor-measure, measure-campaign, and sensor-campaign aka instrument). There is an indirect relationship between sensors and campaigns reflected by the fact that sometimes a sensor and a campaign will have one or more measures in common. There is also a direct relationship between a sensor and a campaign which is independent of whether or not the indirect relationship exists or not.

Now one or both of these analyses is just plain wrong. I lean towards A, even though you have to twist your words to get there. But it's your call.

Why do I put so much emphasis on analysis? Because if the analysis is wrong, the design is pretty much doomed.

The A interpretation needs only three tables and two foreign keys. The two foreign keys (SensorId and CampaignId) both go in the Measures table along with the other columns.

The B interpretation needs four tables like the ones you depicted, except that instead of one linkage between Measures and Instruments, you should have two foreign keys that directly tie Measure to Sensor and Campaign instead of the single linkage you show.

1
  • Measures must be taken within a campaign and, of course, must belong to a sensor (both FKs would be not null, also, if either sensor or campaign is deleted, the measure must disappear). You create a campaign, associate sensors to it and start taking measures. Since both FKs will be not null, I thought it was redundant to have campaign and sensor linked both by measure and instrument, But, I am not sure, what would be the advantage of such redundancy, or what am I missing that makes it necessary? (I think there is a good reason, but I cannot see it). Thanks for you answer! – la_jankong Jul 29 '15 at 19:30
2

First, I think you have misunderstood or are misremembering the professor's point. In a one-to-many (1-m) relationship, if one side is zero, it must be the 'm' side. One is, well, one. By definition it cannot be any other value including zero. And in any 1-m relationship, it is generally understood that "many" includes zero, if for no other reason than the "1" record must be inserted first so until the first related record is inserted, the relationship count is zero.

In a many-to-many (m-n) relationship, a third table is required, but a zero reference count on either side is an anomalous condition which should be prevented from ever occurring.

If the professor made such a claim, surely he provided an example. Can you remember anything about such an example?

As far as your diagram, according to your explanation (including needed clarification in your comment) the independent entities are Instrument and Campaign. Sensors relate to Instrument and Measures relate to Sensors and Campaigns.

This makes Measure an intersection table establishing a m-n relationship between Sensor and Campaign, with the added attributes of the value and timestamp of the measurement at least.

Now, and this is more business rule than particular design constraint, you mention that deleting a sensor should delete all measurements associated with that sensor. This means that all past measurements made with that sensor will disappear from the database. Is that really what you want to happen? Probably not. Maintaining an accurate history will most assuredly be required.

Deleting all measurements upon deleting a campaign, however, could possibly be allowed. But, again, these are business rules. These are not your decisions to make.

Update: In reading thru all the comments and references, think I've found the answer -- or at least can express it in a less confusion way.

We all know that a one-to-many (1-n) relationship means "an X entity may have zero or one relationship to an entity Y and any entity Y may have zero, one or many relationships to entity X." To use @miracle173's example, an engineer may have at most one secretary assigned to them and a secretary may be assigned to many engineers.

This is generally implemented with a pointer in the "One" tuple:

Engineer( EmpID, SecyID,...)
Secretary( EmpID, ...)

If the SecID field is NULL, that engineer has no secretary assigned (the zero condition). But there is an obscure, mostly academic debate of the usefulness of NULLs. (I disagree with the premise of the assertion in that argument, but that is an entirely different subject.) The text of miracle173's link mistakenly indicates the issue as the use of NULLs in relationships. It is instead an issue of any use of NULL in general.

But let's say we wanted to eliminate the use of NULL in this particular relationship.

One-to-many relationships may also be implemented with special intersection tables.

create table Eng_Secy(
    EngID   int  not null references Engineers( EmpID ),
    SecyID  int  not null references Secretaries( EmpID ),
    constraint UQ_Eng_Secy_Eng unique( EngID )
);

(The unique constraint could just as well be implemented by making EngID the PK. Either way, an engineer can be listed only once.)

This fully implements the 1-n relationship between engineers and secretaries, with one difference. For any engineer that has not been assigned a secretary, instead of a NULL in the Engineer tuple, there simply is no record at all for that engineer in the intersection table.

Thus, the actual assertion is this: in order to represent the zero condition of a 1-n relationship -- without the use of a NULL value -- that relationship must be implemented through a third table. The zero condition is then represented by the lack of entry in that table.

Is that what the professor was saying?

13
  • 1
    I think you are claiming that all 1:M relationships are of the (common) 1-1::0-M type. But in many text books, the 1:M is a general term for the 4 cases 1-1::0-M, 0-1::0-M, 1-1::1-M, 0-1::1-M. Some of these cases do need an intermediate table - and perhaps that is what the OP's teacher said. – ypercubeᵀᴹ Jul 30 '15 at 17:03
  • 1
    Not that I can see. Having a NULL on either side of an intersection is nonsensical. It means nothing. Or rather, it means "this (the non-NULL pointer) has no relationship with any entity on the other side." This is just as well shown by no entry at all. – TommCatt Jul 30 '15 at 18:41
  • 1
    as @ypercube already mentioned, there are a lot of texts that state that in a one-to-many relationship both sides can be optional, e.g T.Teory, Database Modeling & Design, p.81f. Also in a one-to-one relationship one or two sides can be optional. – miracle173 Jul 31 '15 at 11:12
  • 1
    @ypercube: you didn't explicitly mention nulls, but the zero of any 0-1::1-m relationship implemented using an intersection table requires a null. For example: one instance of a relationship between X of one table and any Y of another table would be one record (X-Y). This is 1-1. Multiple instances would be a series of records (X-Y1)(X-Y2)...(X-Yn). This is 1-n. Any representation of 0-1 or 0-n would require a null in the X field: (null-Y). This is, at best, a useless entry in an intersection table. So an intersection table is useful only for 1-1, 1-n and m-n representations. – TommCatt Jul 31 '15 at 19:44
  • 2
    Made a chat room – ypercubeᵀᴹ Aug 3 '15 at 16:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.