win 7, db xe 18c, apex 18.2.
---------------------------------------------
I am developing a restaurant management application. There are ingredients to be purchased i.e tomato, olive. Those ingredient are dispatched to the kitchen to be used to make some items i.e Pizza. This Pizza then sold to customers.
- Stock is all about ingredients.
- Stock is increased whenever an inventory when a higher value of qty is created, a purchase is made or ingredients returned from the kitchen. And is decreased whenever a qty is dispatched to the kitchen or an inventory with a lower value of qty is created.
- There is an inventory document which refers to the physical count of ingredients in the warehouse i.e 5 Tomato, 6 Potato, etc... .
- Inventory document could contain one, more or all ingredients.
- if an ingredient "Tomato" mentioned in an inventory document created in May with a qty of "2" then mentioned in another document in June
with qty of 3. Then I need the value of "3" because it's the last one.
- the current qty of an ingredient. Which is the last qty of an ingredient entered in an inventory document plus all quantities purchased of that ingredient after the date of the ingredient's last inventory plus all returns from the kitchen after the date of the ingredient's last inventory.
- When creating a new inventory document, the user needs to know the current qty of one, more or all ingredients.
- The problem is that the query to retrieve the current quantity of ingredients will involve many joins, grouping, group functions and subqueries which will have a very negative impact on performance. So I thought of creating a table to store quantities entered or dispatched from the warehouse whenever a transaction is created i.e a purchase is made or a qty returned.
- Could that be a right solution or could I come across some problems using it?
Thanks a lot.
Edited:
- Here is the script of ingredients, inventory and purchases tables and the query I just wrote to get the current stock,
--------------------------------------------------------
-- DDL for Table INGREDIENT
--------------------------------------------------------
CREATE TABLE "INGREDIENT" ("ID" NUMBER, "NAME" VARCHAR2(50), "DEFAULT_PRICE" NUMBER) ;
--------------------------------------------------------
-- DDL for Table PHYSICAL_INVENTORY_DTL
--------------------------------------------------------
CREATE TABLE "PHYSICAL_INVENTORY_DTL" ("ID" NUMBER, "INGREDIENT_ID" NUMBER, "SYSTEM_QTY" NUMBER, "ACTUAL_QTY" NUMBER, "PHYSICAL_INVENTORY_HDR_ID" NUMBER) ;
COMMENT ON COLUMN "PHYSICAL_INVENTORY_DTL"."SYSTEM_QTY" IS 'Qty according to the system.';
COMMENT ON COLUMN "PHYSICAL_INVENTORY_DTL"."ACTUAL_QTY" IS 'Qty according to the User. ';
--------------------------------------------------------
-- DDL for Table PHYSICAL_INVENTORY_HDR
--------------------------------------------------------
CREATE TABLE "PHYSICAL_INVENTORY_HDR" ("ID" NUMBER, "DOCUMENT_DATE" DATE) ;
--------------------------------------------------------
-- DDL for Table PURCHASE_INVOICE_DTL
--------------------------------------------------------
CREATE TABLE "PURCHASE_INVOICE_DTL" ("ID" NUMBER, "INGREDIENT_ID" NUMBER, "QTY" NUMBER, "PRICE" NUMBER, "PURCHASE_INVOICE_ID" NUMBER) ;
--------------------------------------------------------
-- DDL for Table PURCHASE_INVOICE_HDR
--------------------------------------------------------
CREATE TABLE "PURCHASE_INVOICE_HDR" ("ID" NUMBER, "DOCUMENT_DATE" DATE) ;
REM INSERTING into INGREDIENT
SET DEFINE OFF;
Insert into INGREDIENT (ID,NAME,DEFAULT_PRICE) values (1,'ING1',1);
Insert into INGREDIENT (ID,NAME,DEFAULT_PRICE) values (2,'ING2',2);
Insert into INGREDIENT (ID,NAME,DEFAULT_PRICE) values (3,'ING3',3);
Insert into INGREDIENT (ID,NAME,DEFAULT_PRICE) values (4,'ING4',4);
Insert into INGREDIENT (ID,NAME,DEFAULT_PRICE) values (5,'ING5',12);
REM INSERTING into PHYSICAL_INVENTORY_DTL
SET DEFINE OFF;
Insert into PHYSICAL_INVENTORY_DTL (ID,INGREDIENT_ID,SYSTEM_QTY,ACTUAL_QTY,PHYSICAL_INVENTORY_HDR_ID) values (22,1,11,100,22);
Insert into PHYSICAL_INVENTORY_DTL (ID,INGREDIENT_ID,SYSTEM_QTY,ACTUAL_QTY,PHYSICAL_INVENTORY_HDR_ID) values (23,2,22,250,22);
Insert into PHYSICAL_INVENTORY_DTL (ID,INGREDIENT_ID,SYSTEM_QTY,ACTUAL_QTY,PHYSICAL_INVENTORY_HDR_ID) values (41,1,14,12,42);
REM INSERTING into PHYSICAL_INVENTORY_HDR
SET DEFINE OFF;
Insert into PHYSICAL_INVENTORY_HDR (ID,DOCUMENT_DATE) values (22,to_date('03/05/2019','DD/MM/YYYY'));
Insert into PHYSICAL_INVENTORY_HDR (ID,DOCUMENT_DATE) values (42,to_date('04/12/2019','DD/MM/YYYY'));
--------------------------------------------------------
-- Constraints for Table INGREDIENT
--------------------------------------------------------
ALTER TABLE "INGREDIENT" ADD CONSTRAINT "INGREDIENT_PK" PRIMARY KEY ("ID") USING INDEX ENABLE;
ALTER TABLE "INGREDIENT" MODIFY ("ID" NOT NULL ENABLE);
ALTER TABLE "INGREDIENT" MODIFY ("NAME" NOT NULL ENABLE);
--------------------------------------------------------
-- Constraints for Table PHYSICAL_INVENTORY_DTL
--------------------------------------------------------
ALTER TABLE "PHYSICAL_INVENTORY_DTL" MODIFY ("PHYSICAL_INVENTORY_HDR_ID" NOT NULL ENABLE);
ALTER TABLE "PHYSICAL_INVENTORY_DTL" MODIFY ("ACTUAL_QTY" NOT NULL ENABLE);
ALTER TABLE "PHYSICAL_INVENTORY_DTL" MODIFY ("SYSTEM_QTY" NOT NULL ENABLE);
ALTER TABLE "PHYSICAL_INVENTORY_DTL" MODIFY ("INGREDIENT_ID" NOT NULL ENABLE);
ALTER TABLE "PHYSICAL_INVENTORY_DTL" MODIFY ("ID" NOT NULL ENABLE);
--------------------------------------------------------
-- Constraints for Table PHYSICAL_INVENTORY_HDR
--------------------------------------------------------
ALTER TABLE "PHYSICAL_INVENTORY_HDR" ADD CONSTRAINT "PHYSICAL_INVENTORY_HDR_PK" PRIMARY KEY ("ID") USING INDEX ENABLE;
ALTER TABLE "PHYSICAL_INVENTORY_HDR" MODIFY ("DOCUMENT_DATE" NOT NULL ENABLE);
ALTER TABLE "PHYSICAL_INVENTORY_HDR" MODIFY ("ID" NOT NULL ENABLE);
--------------------------------------------------------
-- Constraints for Table PURCHASE_INVOICE_DTL
--------------------------------------------------------
ALTER TABLE "PURCHASE_INVOICE_DTL" MODIFY ("PURCHASE_INVOICE_ID" NOT NULL ENABLE);
ALTER TABLE "PURCHASE_INVOICE_DTL" MODIFY ("PRICE" NOT NULL ENABLE);
ALTER TABLE "PURCHASE_INVOICE_DTL" MODIFY ("QTY" NOT NULL ENABLE);
ALTER TABLE "PURCHASE_INVOICE_DTL" MODIFY ("INGREDIENT_ID" NOT NULL ENABLE);
ALTER TABLE "PURCHASE_INVOICE_DTL" MODIFY ("ID" NOT NULL ENABLE);
--------------------------------------------------------
-- Constraints for Table PURCHASE_INVOICE_HDR
--------------------------------------------------------
ALTER TABLE "PURCHASE_INVOICE_HDR" ADD CONSTRAINT "PURCHASE_INVOICE_HDR_PK" PRIMARY KEY ("ID") USING INDEX ENABLE;
ALTER TABLE "PURCHASE_INVOICE_HDR" MODIFY ("DOCUMENT_DATE" NOT NULL ENABLE);
ALTER TABLE "PURCHASE_INVOICE_HDR" MODIFY ("ID" NOT NULL ENABLE);
--------------------------------------------------------
-- Ref Constraints for Table PHYSICAL_INVENTORY_DTL
--------------------------------------------------------
ALTER TABLE "PHYSICAL_INVENTORY_DTL" ADD CONSTRAINT "PHYSICAL_INVENTORY_DTL_FK1" FOREIGN KEY ("PHYSICAL_INVENTORY_HDR_ID") REFERENCES "PHYSICAL_INVENTORY_HDR" ("ID") ON DELETE CASCADE ENABLE;
ALTER TABLE "PHYSICAL_INVENTORY_DTL" ADD CONSTRAINT "PHY_INV_DTL_INGREDIENTID_FK" FOREIGN KEY ("INGREDIENT_ID") REFERENCES "INGREDIENT" ("ID") ENABLE;
--------------------------------------------------------
-- Ref Constraints for Table PURCHASE_INVOICE_DTL
--------------------------------------------------------
ALTER TABLE "PURCHASE_INVOICE_DTL" ADD CONSTRAINT "PI_DTL_INGREDIENTID_FK" FOREIGN KEY ("INGREDIENT_ID") REFERENCES "INGREDIENT" ("ID") ENABLE;
ALTER TABLE "PURCHASE_INVOICE_DTL" ADD CONSTRAINT "PURCHASE_INVOICE_DTL_FK1" FOREIGN KEY ("PURCHASE_INVOICE_ID") REFERENCES "PURCHASE_INVOICE_HDR" ("ID") ON DELETE CASCADE ENABLE;
SELECT pih.document_date,
pid.INGREDIENT_ID ingredient_id,
(pid.actual_qty +
(SELECT SUM(QTY) PURCHASED_QTY
FROM PURCHASE_INVOICE_HDR IHDR
JOIN PURCHASE_INVOICE_DTL IDTL
ON IHDR.ID = IDTL.PURCHASE_INVOICE_ID
AND IHDR.DOCUMENT_DATE > PIH.DOCUMENT_DATE
AND IDTL.INGREDIENT_ID = PID.INGREDIENT_ID
)) CURRENT_QTY
FROM physical_inventory_hdr pih
JOIN physical_inventory_dtl pid
ON pih.id = pid.physical_inventory_hdr_id
WHERE pid.physical_inventory_hdr_id =
(SELECT MAX(d.physical_inventory_hdr_id)
FROM physical_inventory_dtl d
WHERE d.INGREDIENT_ID = pid.INGREDIENT_ID
);