Note: This is not an inventory controlling system. I am just trying to map which medication given to which patient. I am not considering how many medication packets etc. Just a single medication event
注:这不是库存控制系统。我只是想画出哪个药物给了哪个病人。我不考虑有多少药包等等，只是一个单一的药物事件
I am having a sudden confusion with database relationships, even after working with them for years. Below is my situation.
我突然对数据库关系感到困惑，即使在与它们一起工作多年之后。下面是我的情况。
I have a table called patient where it will hold the information of patients. I have another table called medication where it will hold the medicines prescribed for patients. I want to find the relationship, so I asked the below questions from me.
我有一张叫“病人”的桌子，里面有病人的信息。我有另一个表格叫做药物，里面有给病人开的药。我想找到这种关系，所以我问了下面的问题。
- Can one patient have many medicine prescribed? - Answer: YES
- 一个病人能开很多药吗?——答案:是的
- Can one prescribed medicine have many patients? - Answer: No (ex: you can't give a patient a paracetamol to drink, take it out and give it to someone else)
- 一种处方药可以有很多病人吗?-答:不(例如:你不能给病人扑热息痛喝，把它拿出来给别人喝)
I need to create the foreign key of patient in medication table. I'm confused, because my answer for 1st question tell me it is one to many relationship while the answer for 2nd says me it is one to one relationship.
我需要在药物表中创建病人的外键。我很困惑，因为我对第一个问题的回答告诉我这是一对多的关系，而第二个回答告诉我这是一对一的关系。
What is the exact relation when I am planning to add the foreign key of patient in medication table?
当我计划在药物治疗表中添加病人的外键时，确切的关系是什么?
Below is my structure
下面是我的结构
5 个解决方案
#1
2
It somewhat depends on the kind of structure of your tables.
这在某种程度上取决于表的结构。
Example 1
示例1
Patient:
PatientID Name
--------- ----
1 John
2 Matt
Patient_Medication:
PrescriptionID PatientID Name
-------------- --------- ------------
1 1 Antacid
2 1 Paracetamol
3 2 Asthma inhaler
You are in a one to many relationship. Patient John can have multiple medications in prescription table.
你处于一到多种关系之中。病人约翰可以在处方桌上有多种药物。
Example 2
示例2
Patient:
PatientID Name
--------- ----
1 John
2 Matt
3 Katie
Medication:
MedicationID Name
------------ ----
1 Antacid
2 Paracetamol
3 Asthma inhaler
Patient_Medication:
ID PatientID MedicationID
--- --------- ------------
1 1 (John) 1 (Antacid)
2 1 (John) 2 (Paracetamol)
3 2 (Matt) 3 (Asthma inhaler)
4 3 (Katie) 2 (Paracetamol)
5 3 (Katie) 3 (Asthma inhaler)
This situation is a many-to-many relationship where many patients can have many medications and vice versa. Usually Patient_Medication is called a junction table.
这种情况是一种多对多的关系，许多患者可以使用多种药物，反之亦然。通常患者用药称为连接表。
#2
1
Your second question:
你的第二个问题:
- Can one prescribed medicine have many patients? - Answer: No (ex: you can't give a patient a paracetamol to drink, take it out and give it to someone else)
- 一种处方药可以有很多病人吗?-答:不(例如:你不能给病人扑热息痛喝，把它拿出来给别人喝)
I guess here you have assumed that prescribing a medication and actually consuming that mediation (actual tablet) in real world is the same thing.
我猜你在这里已经假设了在现实世界中开一种药物处方和实际使用这种中介(真正的药片)是一回事。
The medication table is just a name holder for the medication.
药物表只是药物的名称。
Your answer would have been correct if your table "Medication" would be storing actual instances of medication.
如果您的表格“药物”将存储实际的药物实例，那么您的答案应该是正确的。
E.g.
如。
Medication
药物治疗
Id Name
身份证的名字
1 Paracetomol 25mg Instance 1
1扑热息痛25mg 1例
2 Paracetomol 25mg Instance 2
2扑热息痛25mg实例2
3 Paracetomol 25mg Instance 3
3扑热息痛25mg例3
Now here, table is actually containing medication instances which can not be consumed by two patients. And here your answer "No" is, I guess, correct.
这里，表格中包含的药物实例不能被两个病人使用。在这里，你的答案是“不”，我猜是正确的。
The other thing is, as you said you are not working on inventory system, and just trying to map medication, you are still attached to real world inventory item which cannot be consumed by two patients.
另一件事是，正如你说过的，你不是在做库存系统，只是在尝试绘制药物，你仍然依附于现实世界的库存项目这是两个病人不能消费的。
Here you are mixing inventory item in a system where inventory item is not required.
这里是在系统中混合库存项，其中不需要库存项。
#3
1
I think the relationship should actually be many-to-many. A given patient record could point to several different medications, and similarly a given medication record could point to several different patients.
我认为这种关系应该是多对多的。一个特定的病人记录可以指向几种不同的药物，同样的药物记录可以指向不同的病人。
One way to implement this would be to create a third table which maps patients to medications (or medications to patients, if you prefer to think of it that way). This table might look like this:
实现这一目标的一种方法是创建第三张表，将病人映射到药物(如果你愿意这样想的话)。这个表格可能是这样的:
id | patient_id | medication_id | date
1 | 1 | 1 | 2016-12-19
2 | 1 | 2 | 2016-12-18
3 | 2 | 2 | 2016-12-18
The above data would imply that patient 1 took medications 1 and 2, and medication 2 was also being taken by patient 2. I also added a date, which might be a proxy for a given patient visit.
上述数据表明，患者1服用了药物1和2，患者2也服用了药物2。我还添加了一个日期，它可能是给定病人访问的代理。
The medication_id could be a unique identifier for a given pack of medication delievered. In another table, each unique medication would be related to a parent table for that medication.
medication_id可以是一个唯一的标识符，用于发送给给定的药物包。在另一个表中，每个唯一的药物都与该药物的父表相关。
Update:
更新:
Your current schema does not look far off, except that the table you labelled medication is actually a bridge table between patients and their medication dosages. You would need a third table which stores the metadata for each medication. This metadata would be constant for all medication dosages, e.g. type of drug, cost, etc.
你目前的模式看起来并不遥远，除了你标注的药物治疗表实际上是病人和他们的药物剂量之间的桥梁表。您将需要第三个表来存储每种药物的元数据。这种元数据对于所有药物剂量都是恒定的，例如药物类型、成本等。
#4
0
Could your confusion be the result of not having defined what the medication table actually represents. It seems to me that your are confusing type of medicine and actual packets.
你的困惑可能是由于没有定义药物表实际上代表什么。在我看来，你的药物和实际的包装是令人困惑的。
So what kind of relationship are you trying to model? Are you doing a system that can do inventory of how much medicine you have, or are you doing a patient system that can tell you how many patients are getting a particular medication.
那么你想建立什么样的关系呢?你是否在做一个系统，可以对你有多少药物进行盘点，或者你在做一个病人系统，可以告诉你有多少病人得到了一种特殊的药物。
I think your answer to question two is wrong, many patients can be on the same medication. The number of packets you have in stock should be handled in a separate table where you could hold information on things like how many packets you have, what their location is and so on.
我认为你对问题二的回答是错误的，很多病人可以使用相同的药物。存储的数据包的数量应该放在一个单独的表中，在这个表中您可以保存一些信息，比如您有多少数据包，它们的位置等等。
So you need at least three tables
所以你至少需要三张桌子。
patient - holds the patient medication - holds the types of medication patient_medication - holds the information on what types of medication the patient is on
病人-持有病人的药物-持有病人的药物-持有病人的药物类型-持有病人使用的药物类型的信息
you can then add things like another table to hold information on how much of a medication you have and where it's stored if that is relevant to the system.
然后，您可以添加类似于另一个表的内容，以保存关于您拥有的药物的数量以及与系统相关的药物存储位置的信息。
#5
0
It's not wrong to say that the association is one-to-many in one direction, and one-to-one in the other direction. When planning a database, I often advise people to write out the associations in both directions:
说关联在一个方向上是一对多，在另一个方向上是一对多，这并没有错。在规划数据库时，我经常建议人们在两个方向上写出关联:
- Each patient can have zero or more medications
- 每个病人可以有零或更多的药物
- Each medication belongs to one and only one patient
- 每一种药物只属于一个病人
This helps to determine the cardinality of the relationship and clarify functional dependencies. When only one direction is specified, it can be difficult to distinguish one-to-many from many-to-many associations.
这有助于确定关系的基数，并澄清函数依赖关系。当只指定一个方向时，很难区分一对多和多对多关联。
When talking about the relationship as a whole, we take an "overhead perspective" and ignore the perspective of individual entities, so we would call this example one-to-zero-or-more, or commonly just one-to-many.
当把关系作为一个整体来讨论时，我们使用“开销透视图”，忽略单个实体的透视图，因此我们将这个示例称为一对一或更多，或者通常称为一对多。
Many-to-many relationships look like two one-to-many associations when you view it from the perspective of individual entities on either side.
多对多关系看起来像两个一对多关联，当您从两边的单个实体的角度来看它时。
#1
2
It somewhat depends on the kind of structure of your tables.
这在某种程度上取决于表的结构。
Example 1
示例1
Patient:
PatientID Name
--------- ----
1 John
2 Matt
Patient_Medication:
PrescriptionID PatientID Name
-------------- --------- ------------
1 1 Antacid
2 1 Paracetamol
3 2 Asthma inhaler
You are in a one to many relationship. Patient John can have multiple medications in prescription table.
你处于一到多种关系之中。病人约翰可以在处方桌上有多种药物。
Example 2
示例2
Patient:
PatientID Name
--------- ----
1 John
2 Matt
3 Katie
Medication:
MedicationID Name
------------ ----
1 Antacid
2 Paracetamol
3 Asthma inhaler
Patient_Medication:
ID PatientID MedicationID
--- --------- ------------
1 1 (John) 1 (Antacid)
2 1 (John) 2 (Paracetamol)
3 2 (Matt) 3 (Asthma inhaler)
4 3 (Katie) 2 (Paracetamol)
5 3 (Katie) 3 (Asthma inhaler)
This situation is a many-to-many relationship where many patients can have many medications and vice versa. Usually Patient_Medication is called a junction table.
这种情况是一种多对多的关系，许多患者可以使用多种药物，反之亦然。通常患者用药称为连接表。
#2
1
Your second question:
你的第二个问题:
- Can one prescribed medicine have many patients? - Answer: No (ex: you can't give a patient a paracetamol to drink, take it out and give it to someone else)
- 一种处方药可以有很多病人吗?-答:不(例如:你不能给病人扑热息痛喝，把它拿出来给别人喝)
I guess here you have assumed that prescribing a medication and actually consuming that mediation (actual tablet) in real world is the same thing.
我猜你在这里已经假设了在现实世界中开一种药物处方和实际使用这种中介(真正的药片)是一回事。
The medication table is just a name holder for the medication.
药物表只是药物的名称。
Your answer would have been correct if your table "Medication" would be storing actual instances of medication.
如果您的表格“药物”将存储实际的药物实例，那么您的答案应该是正确的。
E.g.
如。
Medication
药物治疗
Id Name
身份证的名字
1 Paracetomol 25mg Instance 1
1扑热息痛25mg 1例
2 Paracetomol 25mg Instance 2
2扑热息痛25mg实例2
3 Paracetomol 25mg Instance 3
3扑热息痛25mg例3
Now here, table is actually containing medication instances which can not be consumed by two patients. And here your answer "No" is, I guess, correct.
这里，表格中包含的药物实例不能被两个病人使用。在这里，你的答案是“不”，我猜是正确的。
The other thing is, as you said you are not working on inventory system, and just trying to map medication, you are still attached to real world inventory item which cannot be consumed by two patients.
另一件事是，正如你说过的，你不是在做库存系统，只是在尝试绘制药物，你仍然依附于现实世界的库存项目这是两个病人不能消费的。
Here you are mixing inventory item in a system where inventory item is not required.
这里是在系统中混合库存项，其中不需要库存项。
#3
1
I think the relationship should actually be many-to-many. A given patient record could point to several different medications, and similarly a given medication record could point to several different patients.
我认为这种关系应该是多对多的。一个特定的病人记录可以指向几种不同的药物，同样的药物记录可以指向不同的病人。
One way to implement this would be to create a third table which maps patients to medications (or medications to patients, if you prefer to think of it that way). This table might look like this:
实现这一目标的一种方法是创建第三张表，将病人映射到药物(如果你愿意这样想的话)。这个表格可能是这样的:
id | patient_id | medication_id | date
1 | 1 | 1 | 2016-12-19
2 | 1 | 2 | 2016-12-18
3 | 2 | 2 | 2016-12-18
The above data would imply that patient 1 took medications 1 and 2, and medication 2 was also being taken by patient 2. I also added a date, which might be a proxy for a given patient visit.
上述数据表明，患者1服用了药物1和2，患者2也服用了药物2。我还添加了一个日期，它可能是给定病人访问的代理。
The medication_id could be a unique identifier for a given pack of medication delievered. In another table, each unique medication would be related to a parent table for that medication.
medication_id可以是一个唯一的标识符，用于发送给给定的药物包。在另一个表中，每个唯一的药物都与该药物的父表相关。
Update:
更新:
Your current schema does not look far off, except that the table you labelled medication is actually a bridge table between patients and their medication dosages. You would need a third table which stores the metadata for each medication. This metadata would be constant for all medication dosages, e.g. type of drug, cost, etc.
你目前的模式看起来并不遥远，除了你标注的药物治疗表实际上是病人和他们的药物剂量之间的桥梁表。您将需要第三个表来存储每种药物的元数据。这种元数据对于所有药物剂量都是恒定的，例如药物类型、成本等。
#4
0
Could your confusion be the result of not having defined what the medication table actually represents. It seems to me that your are confusing type of medicine and actual packets.
你的困惑可能是由于没有定义药物表实际上代表什么。在我看来，你的药物和实际的包装是令人困惑的。
So what kind of relationship are you trying to model? Are you doing a system that can do inventory of how much medicine you have, or are you doing a patient system that can tell you how many patients are getting a particular medication.
那么你想建立什么样的关系呢?你是否在做一个系统，可以对你有多少药物进行盘点，或者你在做一个病人系统，可以告诉你有多少病人得到了一种特殊的药物。
I think your answer to question two is wrong, many patients can be on the same medication. The number of packets you have in stock should be handled in a separate table where you could hold information on things like how many packets you have, what their location is and so on.
我认为你对问题二的回答是错误的，很多病人可以使用相同的药物。存储的数据包的数量应该放在一个单独的表中，在这个表中您可以保存一些信息，比如您有多少数据包，它们的位置等等。
So you need at least three tables
所以你至少需要三张桌子。
patient - holds the patient medication - holds the types of medication patient_medication - holds the information on what types of medication the patient is on
病人-持有病人的药物-持有病人的药物-持有病人的药物类型-持有病人使用的药物类型的信息
you can then add things like another table to hold information on how much of a medication you have and where it's stored if that is relevant to the system.
然后，您可以添加类似于另一个表的内容，以保存关于您拥有的药物的数量以及与系统相关的药物存储位置的信息。
#5
0
It's not wrong to say that the association is one-to-many in one direction, and one-to-one in the other direction. When planning a database, I often advise people to write out the associations in both directions:
说关联在一个方向上是一对多，在另一个方向上是一对多，这并没有错。在规划数据库时，我经常建议人们在两个方向上写出关联:
- Each patient can have zero or more medications
- 每个病人可以有零或更多的药物
- Each medication belongs to one and only one patient
- 每一种药物只属于一个病人
This helps to determine the cardinality of the relationship and clarify functional dependencies. When only one direction is specified, it can be difficult to distinguish one-to-many from many-to-many associations.
这有助于确定关系的基数，并澄清函数依赖关系。当只指定一个方向时，很难区分一对多和多对多关联。
When talking about the relationship as a whole, we take an "overhead perspective" and ignore the perspective of individual entities, so we would call this example one-to-zero-or-more, or commonly just one-to-many.
当把关系作为一个整体来讨论时，我们使用“开销透视图”，忽略单个实体的透视图，因此我们将这个示例称为一对一或更多，或者通常称为一对多。
Many-to-many relationships look like two one-to-many associations when you view it from the perspective of individual entities on either side.
多对多关系看起来像两个一对多关联，当您从两边的单个实体的角度来看它时。