英文:
Need advice to design database in mongodb with mongoose
问题
我有2个集合:医院和患者。
这个月患者在医院A接受检查,但下个月,这名患者在医院B接受检查。因此,在更新患者信息时,如何保存患者已经在医院A接受检查的历史记录?请给我建议。谢谢。
英文:
I have 2 collections: Hospital and Patient.
This month the patient is examination at hospital A, But next month, this patient is examination at hospital B. So when updating patient information, how to save patient's history that patient is already examination at hospital A? Please give me an advice? Thanks
答案1
得分: 3
你需要为此创建一个单独的检查集合。
(这类似于关系型数据库中的中间(关联)表。)
解决这个问题的一种方法是使用 虚拟填充。
通过虚拟填充,我们不需要保留对检查的引用,这在添加、更新或删除检查时会简化事情。
因为只有检查集合需要被更新。
patient.js
const mongoose = require("mongoose");
const patientSchema = new mongoose.Schema(
{
name: String
},
{
toJSON: { virtuals: true }
}
);
// 虚拟填充
patientSchema.virtual("examinations", {
ref: "Examination",
foreignField: "patientId",
localField: "_id"
});
module.exports = mongoose.model("Patient", patientSchema);
hospital.js
const mongoose = require("mongoose");
const hospitalSchema = new mongoose.Schema(
{
name: String
},
{
toJSON: { virtuals: true }
}
);
// 虚拟填充
hospitalSchema.virtual("examinations", {
ref: "Examination",
foreignField: "hospitalId",
localField: "_id"
});
module.exports = mongoose.model("Hospital", hospitalSchema);
examination.js
const mongoose = require("mongoose");
const examinationSchema = new mongoose.Schema({
when: {
type: Date,
default: Date.now()
},
patientId: {
type: mongoose.Schema.Types.ObjectId,
ref: "Patient"
},
hospitalId: {
type: mongoose.Schema.Types.ObjectId,
ref: "Hospital"
}
});
module.exports = mongoose.model("Examination", examinationSchema);
正如你所看到的,我们的病人和医院模式非常干净,没有任何检查引用。
让我们拥有这些现有的病人。
{
"_id": ObjectId("5e0f86d0ea3eb831a4845064"),
"name": "Patient 1",
"__v": NumberInt(0)
},
{
"_id": ObjectId("5e0f86dbea3eb831a4845065"),
"name": "Patient 2",
"__v": NumberInt(0)
}
让我们拥有这些现有的医院。
{
"_id": ObjectId("5e0f86feea3eb831a4845066"),
"name": "Hospital 1",
"__v": NumberInt(0)
},
{
"_id": ObjectId("5e0f8705ea3eb831a4845067"),
"name": "Hospital 2",
"__v": NumberInt(0)
}
让我们拥有这些现有的检查。
/* Patient 1 - Hospital 1 */
{
"when": "2020-01-03T18:27:12.997Z",
"_id": "5e0f878346e50d41d846d482",
"patientId": "5e0f86d0ea3eb831a4845064",
"hospitalId": "5e0f86feea3eb831a4845066",
"__v": 0
},
/* Patient 1 - Hospital 1 */
{
"when": "2020-01-03T18:27:12.997Z",
"_id": "5e0f87a646e50d41d846d483",
"patientId": "5e0f86d0ea3eb831a4845064",
"hospitalId": "5e0f86feea3eb831a4845066",
"__v": 0
},
/* Patient 1 - Hospital 2*/
{
"when": "2020-01-03T18:27:12.997Z",
"_id": "5e0f87c446e50d41d846d484",
"patientId": "5e0f86d0ea3eb831a4845064",
"hospitalId": "5e0f8705ea3eb831a4845067",
"__v": 0
},
/* Patient 2 - Hospital 1 */
{
"when": "2020-01-03T18:27:12.997Z",
"_id": "5e0f87e046e50d41d846d485",
"patientId": "5e0f86dbea3eb831a4845065",
"hospitalId": "5e0f86feea3eb831a4845066",
"__v": 0
}
现在,如果我们想获取一个病人及其检查的信息,我们可以使用以下代码:
app.get("/patients/:id", async (req, res) => {
const result = await Patient.findById(req.params.id).populate("examinations");
res.send(result);
});
结果将类似于这样:
{
"_id": "5e0f86d0ea3eb831a4845064",
"name": "Patient 1",
"__v": 0,
"examinations": [
{
"when": "2020-01-03T18:27:12.997Z",
"_id": "5e0f878346e50d41d846d482",
"patientId": "5e0f86d0ea3eb831a4845064",
"hospitalId": "5e0f86feea3eb831a4845066",
"__v": 0
},
{
"when": "2020-01-03T18:27:12.997Z",
"_id": "5e0f87a646e50d41d846d483",
"patientId": "5e0f86d0ea3eb831a4845064",
"hospitalId": "5e0f86feea3eb831a4845066",
"__v": 0
},
{
"when": "2020-01-03T18:27:12.997Z",
"_id": "5e0f87c446e50d41d846d484",
"patientId": "5e0f86d0ea3eb831a4845064",
"hospitalId": "5e0f8705ea3eb831a4845067",
"__v": 0
}
],
"id": "5e0f86d0ea3eb831a4845064"
}
我们甚至可以像这样填充医院,内部填充:
英文:
You need to have a separate examination collection for this.
(It is like the intermediate (associative) table in relational databases.)
One way to solve this is using virtual populate.
With virtual populate we don't need to keep references to the examinations, which will simplify things when an examination is added, updated or deleted.
Because only the examinations collection will need to be updated.
patient.js
const mongoose = require("mongoose");
const patientSchema = new mongoose.Schema(
{
name: String
},
{
toJSON: { virtuals: true }
}
);
// Virtual populate
patientSchema.virtual("examinations", {
ref: "Examination",
foreignField: "patientId",
localField: "_id"
});
module.exports = mongoose.model("Patient", patientSchema);
hospital.js
const mongoose = require("mongoose");
const hospitalSchema = new mongoose.Schema(
{
name: String
},
{
toJSON: { virtuals: true }
}
);
// Virtual populate
hospitalSchema.virtual("examinations", {
ref: "Examination",
foreignField: "hospitalId",
localField: "_id"
});
module.exports = mongoose.model("Hospital", hospitalSchema);
examination.js
const mongoose = require("mongoose");
const examinationSchema = new mongoose.Schema({
when: {
type: Date,
default: Date.now()
},
patientId: {
type: mongoose.Schema.Types.ObjectId,
ref: "Patient"
},
hospitalId: {
type: mongoose.Schema.Types.ObjectId,
ref: "Hospital"
}
});
module.exports = mongoose.model("Examination", examinationSchema);
As you see our patient and hospital schemas are very clean without any examination reference.
Let's have these existing patients.
{
"_id" : ObjectId("5e0f86d0ea3eb831a4845064"),
"name" : "Patient 1",
"__v" : NumberInt(0)
},
{
"_id" : ObjectId("5e0f86dbea3eb831a4845065"),
"name" : "Patient 2",
"__v" : NumberInt(0)
}
Let's have these existing hospitals.
{
"_id" : ObjectId("5e0f86feea3eb831a4845066"),
"name" : "Hospital 1",
"__v" : NumberInt(0)
},
{
"_id" : ObjectId("5e0f8705ea3eb831a4845067"),
"name" : "Hospital 2",
"__v" : NumberInt(0)
}
Let's have these existing examinations.
/* Patient 1 - Hospital 1 */
{
"when": "2020-01-03T18:27:12.997Z",
"_id": "5e0f878346e50d41d846d482",
"patientId": "5e0f86d0ea3eb831a4845064",
"hospitalId": "5e0f86feea3eb831a4845066",
"__v": 0
},
/* Patient 1 - Hospital 1 */
{
"when": "2020-01-03T18:27:12.997Z",
"_id": "5e0f87a646e50d41d846d483",
"patientId": "5e0f86d0ea3eb831a4845064",
"hospitalId": "5e0f86feea3eb831a4845066",
"__v": 0
},
/* Patient 1 - Hospital 2*/
{
"when": "2020-01-03T18:27:12.997Z",
"_id": "5e0f87c446e50d41d846d484",
"patientId": "5e0f86d0ea3eb831a4845064",
"hospitalId": "5e0f8705ea3eb831a4845067",
"__v": 0
},
/* Patient 2 - Hospital 1 */
{
"when": "2020-01-03T18:27:12.997Z",
"_id": "5e0f87e046e50d41d846d485",
"patientId": "5e0f86dbea3eb831a4845065",
"hospitalId": "5e0f86feea3eb831a4845066",
"__v": 0
}
Now if we want to get the info of a patient and his/her examinations we can use the following code:
app.get("/patients/:id", async (req, res) => {
const result = await Patient.findById(req.params.id).populate("examinations");
res.send(result);
});
The result will be like this:
{
"_id": "5e0f86d0ea3eb831a4845064",
"name": "Patient 1",
"__v": 0,
"examinations": [
{
"when": "2020-01-03T18:27:12.997Z",
"_id": "5e0f878346e50d41d846d482",
"patientId": "5e0f86d0ea3eb831a4845064",
"hospitalId": "5e0f86feea3eb831a4845066",
"__v": 0
},
{
"when": "2020-01-03T18:27:12.997Z",
"_id": "5e0f87a646e50d41d846d483",
"patientId": "5e0f86d0ea3eb831a4845064",
"hospitalId": "5e0f86feea3eb831a4845066",
"__v": 0
},
{
"when": "2020-01-03T18:27:12.997Z",
"_id": "5e0f87c446e50d41d846d484",
"patientId": "5e0f86d0ea3eb831a4845064",
"hospitalId": "5e0f8705ea3eb831a4845067",
"__v": 0
}
],
"id": "5e0f86d0ea3eb831a4845064"
}
We can even populate the hospital like this with an inner populate:
app.get("/patients/:id", async (req, res) => {
const result = await Patient.findById(req.params.id).populate({
path: "examinations",
populate: {
path: "hospitalId"
}
});
res.send(result);
});
The result will contain the hospital info:
{
"_id": "5e0f86d0ea3eb831a4845064",
"name": "Patient 1",
"__v": 0,
"examinations": [
{
"when": "2020-01-03T18:27:12.997Z",
"_id": "5e0f878346e50d41d846d482",
"patientId": "5e0f86d0ea3eb831a4845064",
"hospitalId": {
"_id": "5e0f86feea3eb831a4845066",
"name": "Hospital 1",
"__v": 0,
"id": "5e0f86feea3eb831a4845066"
},
"__v": 0
},
{
"when": "2020-01-03T18:27:12.997Z",
"_id": "5e0f87a646e50d41d846d483",
"patientId": "5e0f86d0ea3eb831a4845064",
"hospitalId": {
"_id": "5e0f86feea3eb831a4845066",
"name": "Hospital 1",
"__v": 0,
"id": "5e0f86feea3eb831a4845066"
},
"__v": 0
},
{
"when": "2020-01-03T18:27:12.997Z",
"_id": "5e0f87c446e50d41d846d484",
"patientId": "5e0f86d0ea3eb831a4845064",
"hospitalId": {
"_id": "5e0f8705ea3eb831a4845067",
"name": "Hospital 2",
"__v": 0,
"id": "5e0f8705ea3eb831a4845067"
},
"__v": 0
}
],
"id": "5e0f86d0ea3eb831a4845064"
}
Now with this knowledge you can yourself implement the retrieve operations from the hospital side.
答案2
得分: 0
MongoDB不允许像基于SQL的数据库那样进行特定的连接。我建议根据需要创建一个医院集合模式(可能只包含基本类型,如字符串和数字),然后将访问或检查的列表与日期和位置作为患者模式的一部分存储(参见:https://mongoosejs.com/docs/schematypes.html#arrays)。虽然你不能在数据库级别强制执行检查位置是有效的医院,但你可以在代码中强制执行这一点。
英文:
Mongodb does not allow specific joins like a sql based db. I would recommend creating a your hospital collection schema as needed (most likely just basic types such as strings and numbers) and then store a list of visits or examinations with the date and location as part of your patient schema (see: https://mongoosejs.com/docs/schematypes.html#arrays). While you cannot enforce at a database level that the examination location is a valid hospital, you could certainly enforce this in your code.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论