DB Design problems

Question

I have problem on computing the value of item stock in my test retailstore database. Here is the DDL for the table

create table itemtype
(
itemtypeid char(5),
itemtypename varchar(255) not null,
constraint pk_itemid primary key(itemtypeid),
constraint c_itemtypeid check(itemtypeid regexp '^IT[0-9]{3}$')
);


create table item
(
itemid char(5),
itemname varchar(255) not null,
price int not null,
itemtypeid char(5),
constraint pk_itemid primary key(itemid),
constraint fk_itemtype foreign key(itemtypeid) references itemtype(itemtypeid) on delete cascade,
constraint c_itemid check(itemid regexp '^IM[0-9]{3}$')
);

create table supplier
(
supplierid char(5),
suppliername varchar(255),
supplierphone char(12),
constraint pk_supplierid primary key(supplierid),
constraint c_supplierphone check(supplierphone regexp '[0-9]{12}'),
constraint c_supplierid check(supplierid regexp '^SP[0-9]{3}$'),
constraint u_supplierphone unique(supplierphone)
);


create table supplytransaction
(
transactionid char(5),
supplydate date  not null,
supplierid char(5) not null,
constraint pk_transactionid primary key(transactionid),
constraint fk_supply_supplierid foreign key(supplierid) references supplier(supplierid),
constraint c_transid check(transactionid regexp '^TR[0-9]{3}$')
);

create table purchase
(
purchaseid char(5),
purchasedate date  not null,
constraint pk_purchaseid primary key(purchaseid),
constraint c_purchaseid check(purchaseid regexp '^PU[0-9]{3}$')
);

create table detailpurchase
(
purchaseid char(5),
itemid char(5),
purchasequantity int not null,
purchaseprice int not null,
constraint pk_item_purchase primary key(purchaseid,itemid),
constraint c_pqty check(purchasequantity >0),
constraint fk_purchaseid foreign key(purchaseid) references purchase(purchaseid) on delete cascade,
constraint fk_purchase_itemid foreign key(itemid) references item(itemid) on delete cascade
);

create table detailsupplytransaction
(
transactionid char(5),
itemid char(5),
supplyquantity int not null,
price int not null,
constraint pk_item_supply primary key(transactionid,itemid),
constraint c_sqty check(supplyquantity >0),
constraint fk_suply_purchaseid foreign key(transactionid) references supplytransaction(transactionid) on delete cascade,
constraint fk_supply_itemid foreign key(itemid) references item(itemid) on delete cascade
);

The DML to populate the table

Itemtype table

insert into itemtype values('IT001','Cheese');
insert into itemtype values('IT002','Beverage');
insert into itemtype values('IT003','Snack');

item table

insert into item values('IM001','Fried seaweed',11000,'IT003');
insert into item values('IM002','Coca-Cola',20000,'IT002');
insert into item values('IM003','Blue Chiz',10000,'IT001');

Supplier table

insert into supplier values('SP001','Bluechiz guy','624806854886');
insert into supplier values('SP002','chipsdude','023502902081');
insert into supplier values('SP003','Cocacola','387648707137');

Supplytransaction table

insert into supplytransaction values('TR001','2021-01-01','SP001');
insert into supplytransaction values('TR002','2021-01-01','SP002');
insert into supplytransaction values('TR003','2021-01-01','SP003');

purchase table

insert into purchase values('PU001','2021-12-01');

Detailpurchase table

INSERT INTO detailpurchase values('PU001','IM001',3,10000);
INSERT INTO detailpurchase values('PU001','IM002',1,20000);
INSERT INTO detailpurchase values('PU001','IM003',3,11000);

Detailsupply table

insert into detailsupplytransaction values('TR001','IM003',1000,2000);
insert into detailsupplytransaction values('TR002','IM002',150,2000);
insert into detailsupplytransaction values('TR003','IM001',100,2000);

And then I have this query which gets the data of item stock based on the itemtypeid we input in the where clause.

 SELECT 
itemname,sum(supplyquantity)-sum(purchasequantity) stock  
    FROM itemtype it 
    inner join item i on i.itemtypeid=it.itemtypeid
    left join detailpurchase dp on dp.itemid=i.itemid 
    inner join detailsupplytransaction dst on dst.itemid=i.itemid 
    inner join supplytransaction st on st.transactionid=dst.transactionid
    where  itemtypename='Cheese'
    group by itemname;

When we execute above query, everything is fine and with expected result of 997 (1000 (BlueChiz stock) - 3 (got purchased)).

However, when we add another detailsupply transaction, the value is not correct. For example

 insert into supplytransaction values('TR004','2021-01-01','SP001');
insert into detailsupplytransaction values('TR001','IM003',400,2000);

If we re-run the query, the stock is 1394 (1400 - 6 (in the purchase we can see that the purchase quantity of that item is 3 not 6))

This is my core problem, how do I fix this issue? I know some solution is to add same item with different PK value, but I think this is not optimal and kinda redundant. So is there another way to fix this issue?

Answer 1

You are encountering a very common situation for newbie queries known as a Cartesian Result (or cross-apply). Let me describe. When you are doing aggregations from different tables based on a given key, and that key ID exists more than once in either table, you get the product (multiplication) of it.

So, lets look at a purchase table for a single item.

Purchases
ItemID  Item  ItemQty
1       A     5
1       A     7
1       A     9

Sales
ItemID  Item  ItemQty
1       A     2
1       A     1
1       A     3

So, because you are joining on the ItemID (for example), what you are getting is -- for every purchase item, you are getting every SALES item thus

Purchase 5   Sales 2
Purchase 5   Sales 1
Purchase 5   Sales 3
Purchase 7   Sales 2
Purchase 7   Sales 1
Purchase 7   Sales 3
Purchase 9   Sales 2
Purchase 9   Sales 1
Purchase 9   Sales 3

See the problem and why you are seeing the bad math result? In these cases, you should pre-aggregate the purchases and sales separately as their own pre-aggregates based on a given ID so there is only one record on each side of the join thus

sum( Purchase ) = 5 + 7 + 9 = 21
sum( Sales ) = 2 + 1 + 3 = 6

Now, how do you write it.

SELECT
      itemname,
      coalesce( st.SumOfSupply, 0 )
         - coalesce( dp.SumOfPurchase, 0 )  stock  
   FROM 
      itemtype it 
         inner join item i 
            on it.itemtypeid = i.itemtypeid
            left join 
            ( select itemid, sum( purchasequantity ) SumOfPurchase
                 from detailpurchase 
                 group by itemid ) dp
               on i.itemid = dp.itemid
            inner join 
            ( select itemid, sum( supplyquantity ) SumOfSupply
                 from supplytransaction 
                 group by itemid ) st 
               on dst.transactionid = st.transactionid
             inner join 
                detailsupplytransaction dst 
                   on i.itemid = dst.itemid
   where
      itemtypename = 'Cheese'
   group by 
      itemname;

Now, you did not mention about your detail supply transaction table, but I am betting that too may be an issue that could pose an aggregation issue, but leave that to you to follow along. Better you UNDERSTAND WHY the issue and know HOW to fix it, than always rely on asking. No problem in asking, just helping to advise scenarios you may encounter in the future.