Oracle 数据建模问题

请参阅下面和livesql中的脚本。对这些关系进行建模的最佳选择是什么，这些选择会阻止将一条记录插入到supplier_delions表中，该表的supplier_site_id与作为协议所有者的supplier_id无关？

create table SUPPLIERS ( 
    SUPPLIER_ID number not null constraint suppliers_pk primary key, 
    NAME varchar2(45) not null 
)
/

create table SUPPLIER_SITES ( 
    SITE_ID number not null constraint supplier_sites_pk primary key, 
    NAME varchar2(45) not null, 
    SUPPLIER_ID number not null 
)
/

ALTER TABLE SUPPLIER_SITES ADD CONSTRAINT SUPPLIER_SITES_FK1 FOREIGN KEY (SUPPLIER_ID) REFERENCES SUPPLIERS (SUPPLIER_ID) 
/

create table SUPPLIER_AGREEMENTS ( 
    AGREEMENT_NO varchar2(30) not null constraint supplier_agreements_pk primary key, 
    SUPPLIER_ID number not null 
)
/

create table SUPPLIER_DELIVERIES ( 
    DELIVERY_ID number not null constraint supplier_deliveries_pk primary key, 
    AGREEMENT_NO varchar2(30) not null, 
    SUPPLIER_SITE_ID number not null, 
    DELIVERY_DATE date not null 
)
/

ALTER TABLE SUPPLIER_DELIVERIES ADD CONSTRAINT SUPPLIER_DELIVERIES_FK1 FOREIGN KEY (AGREEMENT_NO) REFERENCES SUPPLIER_AGREEMENTS (AGREEMENT_NO)
/

ALTER TABLE SUPPLIER_DELIVERIES ADD CONSTRAINT SUPPLIER_DELIVERIES_FK2 FOREIGN KEY (SUPPLIER_SITE_ID) REFERENCES SUPPLIER_SITES (SITE_ID) 
/

INSERT INTO SUPPLIERS(SUPPLIER_ID, NAME) VALUES(1, 'SUPPLIER 1') 
/

INSERT INTO SUPPLIERS(SUPPLIER_ID, NAME) VALUES(2, 'SUPPLIER 2') 
/

INSERT INTO SUPPLIER_SITES(SITE_ID, NAME, SUPPLIER_ID) VALUES(1, 'SUPPLIER 1 SITE A', 1) 
/


INSERT INTO SUPPLIER_SITES(SITE_ID, NAME, SUPPLIER_ID) VALUES(2, 'SUPPLIER 2 SITE B', 2) 
/

INSERT INTO SUPPLIER_AGREEMENTS(AGREEMENT_NO, SUPPLIER_ID) VALUES('111', 1) 
/

INSERT INTO SUPPLIER_AGREEMENTS(AGREEMENT_NO, SUPPLIER_ID) VALUES('222', 2) 
/

--what is the best way to prevent this insert from occurring successfully?
INSERT INTO SUPPLIER_DELIVERIES(DELIVERY_ID, AGREEMENT_NO, SUPPLIER_SITE_ID, DELIVERY_DATE) VALUES(1, '111', 2, TRUNC(SYSDATE)) 
/

我看到三个基本选项:

-在站点和协议之间创建M:M联接表 (例如supplier_agreement_sites)
-将供应商id添加到供应商交货
-使供应商协议成为供应商网站的子公司 (反之亦然)

M:M Join Table

这存储了供应商的所有协议和站点:

create table supplier_agreement_sites (
  supplier_id number not null ,
  agreement_no varchar2(30) not null , 
  site_id number not null ,
  primary key (
    supplier_id, agreement_no, site_id 
  )
);

然后，您可以将供应商 _ 交货作为此表的子级。因此，您只能插入该供应商的交货地点。

但这打破了第四范式，使这张表上的DML复杂化。每次为供应商注册新站点时，您都需要在此表中插入所有协议。相反。管理更新和删除也很棘手。

除非其他选项对您不可行，否则请避免这种情况。

Add supplier_id to deliveries

create table supplier_deliveries ( 
  delivery_id number not null 
    constraint supplier_deliveries_pk primary key, 
  agreement_no varchar2(30) not null, 
  supplier_site_id number not null, 
  delivery_date date not null,
  supplier_id number not null
);

然后，您可以在以下位置添加唯一约束:

supplier_agreements (supplier_id, agreement_no )
supplier_sites (supplier_id, site_id )

以及从交付表中的这些列到相应父级中的唯一键的外键。

它给出了一个如下的模式:

create table suppliers ( 
  supplier_id number not null 
    constraint suppliers_pk primary key, 
  name varchar2(45) not null 
)
/

create table supplier_sites ( 
  site_id number not null 
    constraint supplier_sites_pk primary key, 
  name varchar2(45) not null, 
  supplier_id number not null 
    constraint supplier_sites_fk1 
    references suppliers (supplier_id),
  constraint supplier_sites_u unique (
    supplier_id, site_id
  )
)
/

create table supplier_agreements ( 
  agreement_no varchar2(30) not null 
    constraint supplier_agreements_pk primary key, 
  supplier_id number not null,
  constraint supplier_agreements_u unique (
    supplier_id, agreement_no
  )
)
/

create table supplier_deliveries ( 
  delivery_id number not null 
    constraint supplier_deliveries_pk primary key, 
  agreement_no varchar2(30) not null
    constraint supplier_deliveries_fk1 
    references supplier_agreements (agreement_no), 
  supplier_site_id number not null
    constraint supplier_deliveries_fk2 
    references supplier_sites (site_id) , 
  delivery_date date not null,
  supplier_id number not null,
  constraint supplier_deliveries_fk3
    foreign key (supplier_id, supplier_site_id)
    references supplier_sites (supplier_id, site_id),
  constraint supplier_deliveries_fk4
    foreign key (supplier_id, agreement_no)
    references supplier_agreements (supplier_id, agreement_no)
)
/

insert into suppliers(supplier_id, name) 
  values (1, 'SUPPLIER 1') 
/

insert into suppliers(supplier_id, name) 
  values (2, 'SUPPLIER 2') 
/

insert into supplier_sites(site_id, name, supplier_id) 
  values (1, 'SUPPLIER 1 SITE A', 1) 
/

insert into supplier_sites(site_id, name, supplier_id) 
  values (2, 'SUPPLIER 2 SITE B', 2) 
/

insert into supplier_agreements(agreement_no, supplier_id) 
  values ('111', 1) 
/

insert into supplier_agreements(agreement_no, supplier_id) 
  values ('222', 2) 
/

insert into supplier_deliveries (delivery_id, agreement_no, supplier_site_id, delivery_date, supplier_id) 
  values(1, '111', 2, trunc(sysdate), 1) 
/

ORA-02291: integrity constraint (CHRIS.SUPPLIER_DELIVERIES_FK3) violated - parent key not found

根据我的假设，这是我的首选选项。

Make one table a child of the other

这意味着您必须将协议链接到站点 (反之亦然)。因此，协议涵盖了供应商向哪些站点提供服务。例如:

供应商1，协议X涵盖现场1、2、3
供应商1，协议Y涵盖现场3、4、5

因为这不在当前的数据模型中，所以我假设这不适合业务的工作方式。当然，如果协议和站点之间存在关系，这是解决此问题的正确方法!