如何在数据库中建模这么多关系？

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

Add a junction table:

添加联结表:

ManagementPlanProduct(PlanID(PK, FK(ManagementPlan)), PMCProductID(PK, FK(Product)))

#2

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