We have two tables ManagementPlan which tells what type of product model has to be used.Based on this model number, a particular product of that type is assigned to a patient during therapy session.How can we model the many-many relation between Product table and ManagementPlan table
我们有两个表ManagementPlan,它告诉我们必须使用哪种类型的产品模型。基于这个型号,在治疗期间将这种类型的特定产品分配给患者。我们如何建模产品表之间的多对多关系和ManagementPlan表
MangamentPlan(
PlanID(PK),
DiagnosisID(FK),
PhysicianID(FK),
PMCModelID,
Objective,
Description,
DateTime
)
Product(
PMCProductID(PK),
ManuProductID(FK),
ManufacturerID(FK),
PMCModelID,
Manufacturer model,
Features description,
PurchaseDate,
Storage Location
)
2 个解决方案
#1
1
Add a junction table:
添加联结表:
ManagementPlanProduct(PlanID(PK, FK(ManagementPlan)), PMCProductID(PK, FK(Product)))
#2
0
You need a junction table
你需要一个联结表
ManagementPlanProduct (
PlanID (PK, FK)
PMCProductID (PK, FK)
)
CREATE TABLE ManagementPlanProduct (
PlanID int NOT NULL,
PMCProductID int NOT NULL,
CONSTRAINT PK_mpp PRIMARY KEY CLUSTERED (PlanID, PMCProductID)
);
ALTER TABLE ManagementPlanProduct
ADD CONSTRAINT FK_mpp_mp
FOREIGN KEY (PlanID) REFERENCES ManagementPlan (PlanID)
ON DELETE CASCADE;
ALTER TABLE ManagementPlanProduct
ADD CONSTRAINT FK_mpp_p
FOREIGN KEY (PMCProductID) REFERENCES Product (PMCProductID)
ON DELETE CASCADE;
You can also add other columns to the junction table like quantity, date added, sort order and so on.
您还可以在联结表中添加其他列,如数量,添加日期,排序顺序等。
#1
1
Add a junction table:
添加联结表:
ManagementPlanProduct(PlanID(PK, FK(ManagementPlan)), PMCProductID(PK, FK(Product)))
#2
0
You need a junction table
你需要一个联结表
ManagementPlanProduct (
PlanID (PK, FK)
PMCProductID (PK, FK)
)
CREATE TABLE ManagementPlanProduct (
PlanID int NOT NULL,
PMCProductID int NOT NULL,
CONSTRAINT PK_mpp PRIMARY KEY CLUSTERED (PlanID, PMCProductID)
);
ALTER TABLE ManagementPlanProduct
ADD CONSTRAINT FK_mpp_mp
FOREIGN KEY (PlanID) REFERENCES ManagementPlan (PlanID)
ON DELETE CASCADE;
ALTER TABLE ManagementPlanProduct
ADD CONSTRAINT FK_mpp_p
FOREIGN KEY (PMCProductID) REFERENCES Product (PMCProductID)
ON DELETE CASCADE;
You can also add other columns to the junction table like quantity, date added, sort order and so on.
您还可以在联结表中添加其他列,如数量,添加日期,排序顺序等。