Oracle database 12c, windows 7, APEX 18.2.
Hi,
Designing a database for a restaurant management system, where users can buy and return items. I have two master-detail tables for purchase and the same goes for returns.
A purchase invoice could be returned in many purchase return documents.
User should not be able to return a purchase invoice number that does not exist, hence the foreign key constraint(PRH_PIID_PIH_ID_FK) in purchase_return_hdr.
User should not be able to return a purchase invoice detail id if it does not exist in purchase_invoice_dtl and belongs to the purchase invoice id in the purchase_return_hdr that user tries to create. And this is my problem. If I create a foreign key in purchase_return_dtl(purchase_invoice_dtl_id) that references purchase_invoice_dtl(id), it will only guarantee that the record being inserted in purchase_return_dtl exists in purchase_invoice_dtl but does not guarantee it belongs to the purchase invoice id in the return document being created.
In a nutshell, the problem is as follows,
- If you have two purchase invoices, each has a master record i.e (1 and 2) and each has one detail record i.e (3 and 4) respectively. Create a purchase return document and insert 1 for the purchase_invoice_id column in the master table purchase_return_hdr and create a detail record and insert 4 in purchase_invoice_dtl_id column. It will accept it although 4 does not belong to invoice 1, it belongs to invoice number 2. Is there a way to solve this problem?
Edited:
User should not be able to insert the same item with the same expiry_date in the same invoice.
Not all items have expiry dates. Some has their's null.
--------------------------------------------------------
-- DDL for Sequence MISC
--------------------------------------------------------
CREATE SEQUENCE "MISC" MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE NOPARTITION ;
--------------------------------------------------------
-- DDL for Table PURCHASE_INVOICE_DTL
--------------------------------------------------------
CREATE TABLE "PURCHASE_INVOICE_DTL" ("ID" NUMBER DEFAULT ON NULL "MISC"."NEXTVAL", "ITEM_ID" NUMBER, "EXPIRY_DATE" DATE, "HDR_ID" NUMBER) ;
--------------------------------------------------------
-- DDL for Table PURCHASE_INVOICE_HDR
--------------------------------------------------------
CREATE TABLE "PURCHASE_INVOICE_HDR" ("ID" NUMBER DEFAULT ON NULL "MISC"."NEXTVAL") ;
--------------------------------------------------------
-- DDL for Table PURCHASE_RETURN_DTL
--------------------------------------------------------
CREATE TABLE "PURCHASE_RETURN_DTL" ("ID" NUMBER DEFAULT ON NULL "MISC"."NEXTVAL", "ITEM_ID" NUMBER, "EXPIRY_DATE" DATE, "HDR_ID" NUMBER, "PURCHASE_INVOICE_DTL_ID" NUMBER) ;
--------------------------------------------------------
-- DDL for Table PURCHASE_RETURN_HDR
--------------------------------------------------------
CREATE TABLE "PURCHASE_RETURN_HDR" ("ID" NUMBER DEFAULT ON NULL "MISC"."NEXTVAL", "PURCHASE_INVOICE_ID" NUMBER) ;
REM INSERTING into PURCHASE_INVOICE_DTL
SET DEFINE OFF;
REM INSERTING into PURCHASE_INVOICE_HDR
SET DEFINE OFF;
REM INSERTING into PURCHASE_RETURN_DTL
SET DEFINE OFF;
REM INSERTING into PURCHASE_RETURN_HDR
SET DEFINE OFF;
--------------------------------------------------------
-- Constraints for Table PURCHASE_INVOICE_DTL
--------------------------------------------------------
ALTER TABLE "PURCHASE_INVOICE_DTL" ADD CONSTRAINT "PURCHASE_INVOICE_DTL_PK" PRIMARY KEY ("ID") USING INDEX ENABLE;
--------------------------------------------------------
-- Constraints for Table PURCHASE_INVOICE_HDR
--------------------------------------------------------
ALTER TABLE "PURCHASE_INVOICE_HDR" ADD CONSTRAINT "PURCHASE_INVOICE_HDR_PK" PRIMARY KEY ("ID") USING INDEX ENABLE;
--------------------------------------------------------
-- Constraints for Table PURCHASE_RETURN_DTL
--------------------------------------------------------
ALTER TABLE "PURCHASE_RETURN_DTL" ADD CONSTRAINT "PURCHASE_RETURN_DTL_PK" PRIMARY KEY ("ID") USING INDEX ENABLE;
--------------------------------------------------------
-- Constraints for Table PURCHASE_RETURN_HDR
--------------------------------------------------------
ALTER TABLE "PURCHASE_RETURN_HDR" ADD CONSTRAINT "PURCHASE_RETURN_HDR_PK" PRIMARY KEY ("ID") USING INDEX ENABLE;
--------------------------------------------------------
-- Ref Constraints for Table PURCHASE_INVOICE_DTL
--------------------------------------------------------
ALTER TABLE "PURCHASE_INVOICE_DTL" ADD CONSTRAINT "PURCHASE_INVOICE_DTL_FK1" FOREIGN KEY ("HDR_ID") REFERENCES "PURCHASE_INVOICE_HDR" ("ID") ENABLE;
--------------------------------------------------------
-- Ref Constraints for Table PURCHASE_RETURN_DTL
--------------------------------------------------------
ALTER TABLE "PURCHASE_RETURN_DTL" ADD CONSTRAINT "PRD_HDRID_PRH_ID_FK" FOREIGN KEY ("HDR_ID") REFERENCES "PURCHASE_RETURN_HDR" ("ID") ENABLE;
ALTER TABLE "PURCHASE_RETURN_DTL" ADD CONSTRAINT "PRD_PIDTLID_PID_ID_FK" FOREIGN KEY ("PURCHASE_INVOICE_DTL_ID") REFERENCES "PURCHASE_INVOICE_DTL" ("ID") ENABLE;
--------------------------------------------------------
-- Ref Constraints for Table PURCHASE_RETURN_HDR
--------------------------------------------------------
ALTER TABLE "PURCHASE_RETURN_HDR" ADD CONSTRAINT "PRH_PIID_PIH_ID_FK" FOREIGN KEY ("PURCHASE_INVOICE_ID") REFERENCES "PURCHASE_INVOICE_HDR" ("ID") ENABLE;
Edited:
Here is the ERD model for the tables involved:
