OneToOne relationship in java

[obaid abbassi]

I have two entities patient and doctor I want to build relation between patient and doctor

like

@Entity
Class Patient{
@Id
private int pId;
private int pName;

@OneToOne
private Doctor doctor;

}

@Entity
Class Doctor{
@Id
private int dId;
private int dName;

@OneToOne
private Patient patient;
}

Now I want to get dId in patient table and pId in Doctor table vice versa
is this a valid mapping?

[Himai Minh]

Hi, i think it is better to have many to one relationship between patients and doctor.
And the relationship is bi-directional.

@Entity
Class Patient{
@Id
private int pId;
private int pName;
 
@ManyToOne
private Doctor doctor;
 
}

 

@Entity
Class Doctor{
@Id
private int dId;
private int dName;
 
@OneToMany (mappedBy=doctor)
private Set<Patient>patients = new HashSet<>();
}

[Rob Spoor]

A one-to-one between patient and doctor means that every patient has his/her own dedicated doctor that does not have any other patients. I doubt that's correct. I therefore agree with Himai that a many-to-one would be better.

That said, if you ever have a one-to-one, it's very uncommon to have the bidirectional relationship at a database level too. The logical one-to-one is mostly actually a many-to-one in the database, with a unique constraint that the many side is limited to just one. Otherwise the two records get really tightly coupled, which has several complications. For instance, on at least one side the foreign key cannot be NULL, because a record has to be created first before its primary key can be used for a foreign key - and you simply cannot create two records at the same time.

[Tim Holloway]

Rob Spoor wrote:A one-to-one between patient and doctor means that every patient has his/her own dedicated doctor that does not have any other patients. I doubt that's correct. I therefore agree with Himai that a many-to-one would be better.

That said, if you ever have a one-to-one, it's very uncommon to have the bidirectional relationship at a database level too. The logical one-to-one is mostly actually a many-to-one in the database, with a unique constraint that the many side is limited to just one. Otherwise the two records get really tightly coupled, which has several complications. For instance, on at least one side the foreign key cannot be NULL, because a record has to be created first before its primary key can be used for a foreign key - and you simply cannot create two records at the same time.

Um, I'm hoping I'm not reading what I think I'm reading there.

If you have a 1 doctor/many patients relationship then very definitely in JPA you should be mapping in both directions in your Entity definitions. The actual database will then have a unique ID in the doctor table and a foreign key for that doctor ID in the patient table. Note that in real life a patient may be under the care of more than one doctor, but before we tackle many-to-many, let's just look at one-to-many.

It is possible to add 2 related records at the same time in the same transaction, although if you're using auto-generated keys, that means that you have to first get the doctor record saved and populated with its primary key. The patient Entity does not have the doctor ID in it, because this is an ORM and it references a Doctor object which, again, we just generated an ID for, so at that point, the patient record can be saved. The doctor Entity contains a Patient Collection property managing the reverse relationship, so you also have to add the Patient there and re-save the updated Doctor.

Of course, again, in real life, you'd probably already have set up the doctor in the database before adding patients, but in a hypothetical single-transaction many-to-one, this is what you'd do. The same basic process does apply to a One-to-One relationship as well.

This isn't a "java" question, it's an ORM/JPA question. so I'm going to ensure it gets properly filed in the right forum if it hasn't been already.

[Rob Spoor]

I think you misread me. I didn't say you shouldn't have a bidirectional relationship in JPA (it's indeed very common to have one), just that you shouldn't have one in the database.

Tim Holloway wrote:It is possible to add 2 related records at the same time in the same transaction, although if you're using auto-generated keys, that means that you have to first get the doctor record saved and populated with its primary key. The patient Entity does not have the doctor ID in it, because this is an ORM and it references a Doctor object which, again, we just generated an ID for, so at that point, the patient record can be saved. The doctor Entity contains a Patient Collection property managing the reverse relationship, so you also have to add the Patient there and re-save the updated Doctor.

I am aware of that, but it requires one of the ID columns to be nullable, and it needs to be set afterwards like you said. If that updating isn't done (for instance if someone updates the database manually), the doctor will miss its relation to the patient in the database.
If you don't use auto-generated keys, you either need to have the nullable column, or don't use referential integrity constraints on the foreign key. Otherwise you still won't be able to set the column with the value, because at the time of insert the record still doesn't exist.

[Tim Holloway]

I couldn't make sense of that.  In the database itself, the Doctor table does NOT have foreign key values for the Patients. You add the Doctor independently and get its ID.

At the ORM level, you don't deal with keys directly either. Once you have a valid Doctor Entity (with key assigned), you THEN add the Patients to the Doctor's Patient Collection This is an ORM-only thing, again, nothing in the Doctor table, only the Doctor Entity Object. Once that has been done,, you save the Patient collection, updating the Doctor and commit the transaction.

If necessary, I'll post some working code, but since yhe code in question uses compound keys that is an extra layer of wartiness on the process, plus since the database in question was originally on an IBM iSeries mainframe where traditionally the text fields were fixed-length, there's also value normalization code cluttering it up.

[Rob Spoor]

I was referring to this:

obaid abbassi wrote:Now I want to get dId in patient table and pId in Doctor table vice versa

Because yes, you are absolutely right. I'd do it the way you described it.

[Tim Holloway]

Rob Spoor wrote:I was referring to this:

obaid abbassi wrote:Now I want to get dId in patient table and pId in Doctor table vice versa

Because yes, you are absolutely right. I'd do it the way you described it.

OK. Yeah. That's workable since the original question was for a 1-to-1 relationship. Though I'd have to check which of several strategies I can think of would allow the resolution of the "simultaneous" key generation problem. I have some code somewhere that dealt with that as well, but I can't recall where.

We did get a little distracted by the fact that the question was about 1-1 when the table names would normally be used in a 1-many situation.

[obaid abbassi]

I got the duplicate key problem in db
because patientID is repeating if same patient takes appointment with another doctor
is it caused for OneToOne relation?

[Tim Holloway]

Yes, if a patient is connected to 2 doctors at the same time, that is a one-to-many relationship, not 1-to-1.

[obaid abbassi]

So should I change patient to OneToMany and doctor to ManyToOne??