database design for inventory

[Anne]

I'm new in designing database. Hope if anyone can help
with the problems that i'm facing now. I created a
database to keep track of my stocks. I have tables:
1.customers (customerID, customerName)
2.customer orders (Invoice#,InvoiceDate,CustomerID,etc)
3.Customer orders details (OrderDetailID, Invoice#,
Quantity, ProductID,etc)
4. products (ProductID, ProductName)
5. Company orders(TrnxID, PO#, SupplierID, PO Date etc)
6. Company ordersDetails (PO#, Quantity, ProductID, etc)

End of the day, I will need to keep track of monthly and
daily stock balance. I'm not sure whether there are flaws
in my designing, or i'm using incorrect ways, seem likes
every method that tried is not working, records are not
tally.

Please assists. Thanks

 

[Tim Ferguson]

[email protected]:

1.customers (customerID, customerName)
2.customer orders (Invoice#,InvoiceDate,CustomerID,etc)
3.Customer orders details (OrderDetailID, Invoice#,
Quantity, ProductID,etc)
4. products (ProductID, ProductName)
5. Company orders(TrnxID, PO#, SupplierID, PO Date etc)
6. Company ordersDetails (PO#, Quantity, ProductID, etc)

End of the day, I will need to keep track of monthly and
daily stock balance. I'm not sure whether there are flaws
in my designing, or i'm using incorrect ways, seem likes
every method that tried is not working, records are not
tally.

Because of your design, you need to append the order details tables using
a UNION query.

SELECT ALL ProductID,
Sum(NumberOrdered) AS NumberOfItems
FROM (
SELECT ALL ProductID, NumOrdered FROM CustOrderDetails
UNION ALL ProductID, NumOrdered FROM CompOrderDetails
)
GROUP BY ProductID
HAVING Sum(NumberOrdered)>0


If you find this is very ugly, you could rethink your design, so that
Customers and Companies become subtypes of a single entity called
EntityThatCanRaiseOrders, and then join the single OrderDetails table to
that table instead.


Hope that helps


Tim F