ER Diagram - Showing Deliveries to Office and to its Branches

Question

For a small project, I am creating an entity relationship diagram for a simple stock-tracking app.

User Story

Products are sold by product suppliers. Products are ordered by an office and delivered to them. One or more deliveries may be required to full-fill an order. These products ordered by this office are in turn delivered to various branches. Is there a general way that I can represent how stock will be distributed by the office to the branches it is responsible for?

ER Diagram

Here is a very simplified diagram describing the above.

Deliveries will be made to an office and in turn the branches. Each department which is child of HeadQuarters(not shown in diagram) has different quantities of stock that do not necessarily have one to one correspondence with the OrdersDetail. The problem is how to show the inventories of the various departments given the current schema or to modify it in such a way that this is easier to show.

Update: Started bounty and created new ERD diagram.

Answer 1

This is a bit of an odd structure. Normally the way I would handle this wouldn't be with a daisy-chain structure that you have here, but would in turn use some sort of transaction-based system.

The way I'd handle it is to have everything off of order, and have one-to-many relationships off of that.

For instance, I do see that you have that with OrderDetail off of Order, however this will always be a subset of Order. All orders will always have detail; I'd link OrderDelivery off of the main Order table, and have the detail accessible at any point as just a reference table off of it instead of off of OrderDetailDelivery.

I'd have Office as a field on OrderDelivery and also use Branch in that way as well. If you want to have separate tables for them, that is fine, but I'd use OrderDelivery as a central place for these. A null could indicate whether it had been delivered or not, and then you could use your application layer to handle the order of the process.

In other words, OfficeID and BranchID could exist as fields to indicate a foreign key to their respective tables off of OrderDelivery

Edit

Since the design has changed a bit (and it does look better), one thing I'd like to point out is that you have supplier with the same metadata as Delivery. Supplier to me sounds like an entity, whereas Delivery is a process. I'd think that Supplier might live well on it's own as a reference table. In other words, you don't need to include all of the same metadata on this table; instead you might want to create a table (much like you have right now for supplier) but instead called SupplierDelivery.

The point I see is that you would like to be able to track all of the pieces of an order of various products through all of its checkpoints. With this in mind you might not necessarily want to have a separate entity for this, but instead track something like SupplierDate as one of the fields on Delivery. Either way I wouldn't get too hung up on the structure; your application layer will be handling a good deal of this.

One thing I'd be very careful about: if multiple tables have fields with the same name, but are not keys referencing each other, you may wish to create distinct names. For example, if deliveryDate on supplier is different from the same key on Delivery, you might want to think about calling it something like shipDate or if you mean the date it arrived at the supplier, supplierDeliveryDate otherwise you can confuse yourself a lot in the future with your queries, and will make your code extremely difficult to parse without a lot of comments.

Edit to include diagram [edited again for a better diagram]:

Below is how I'd handle it. Your redone diagram is pretty close but here are a few changes

My explanation:

It's easiest to set this up with the distinct entities first, and then set up their relationships afterward, and determine whether or not there needs to be a link table.

The distinct entities are as described:

• Order
• Product
• Supplier
• Branch

Headquarters, while I included it, is actually not a necessary component of the diagram; presumably orders and requests are made here, but my understanding is that at no point does the order flow through the headquarters; it is more of a central processing area. I gather products do not run through Headquarters, but instead go directly to branches. If they do (which might slow down delivery processes, but that's up to you), you can replace Branch with it, and have branch as a link of it as you had before. Otherwise, I think you'd be safe to remove it from the diagram entirely.

Link Tables

These are set up for the many-to-many relationships which arise.

• OrderProductDetail - orders can have many products, and many orders can have the same product. Each primary key combo can be associated with a number of products per order [edit: see below, this now ties together orders, products and suppliers, through SupplierProduct]. Because this is a link table, you can have any number of products per order.
• SupplierProduct - this operates on the assumption that there is more than one supplier for the same product, and that one supplier might have multiple products, so this link table is created to handle the inventory available per product. Edit: this is now the direct link to OrderProductDetail as it makes sense that individual suppliers have a link to the order, instead of two tables removed This can serve as a central link to combine suppliers and products, but then tied to OrderProducDtail. Because this is a link table, you can have any number of suppliers delivering any number or amount of product.
• Delivery - Branches can receive many deliveries, and as you mentioned, an order may be split up into various pieces, based on availability. For this reason, this links to OrderProductDetail which is what holds the specific amounts with each product. Since OrderProductDetail is already a link table with dual primary keys, orderId has a foreign key of the dual primary key off of OrderProductDetail using the paired keys of productId and orderId to make sure there is a distinct association with the specific product within a larger order.

To sum this up, supplierProduct contains the combination of suppliers and product which then passes to OrderProductDetail which combines these with the details of the orders. This table essentially does the bulk of the work to put everything together before it passes through a delivery to the branches.

Note: Last edit: Added supplier id to OrderProductDetail and switched to the dual primary key of supplierId and productId from supplierProduct to make sure you have a clearer way of making sure you can be granular enough in the way the products go from suppliers to OrderProductDetail.

I hope this helps.

Answer 2

I hope this will solve your problem. If there are issues, let me know.

Thanks

Answer 3

I would suggest you to simplify your ER in the following way

Then define the tables

Suppliers(SupplierId) where SupplierId is PK
Products(ProductId) where ProductId is PK
HeadQuarters(HeadQuarterId) where HeadQuarterId is PK
Branches(BranchId, HeadQuarterId) where BranchId is PK and HeadQuarterId references HeadQuarters
Orders(OrderId, OrderDate, SupplierId) where OrderId is PK and SupplierId references Suppliers
Deliveries(OrderId, DeliveryNumber, DeliveryDate, BranchId) where (OrderId, DeliveryNumber) is PK, OrderId references Orders and BranchId referenced Branches
DeliveriedProducts(OrderId, DeliveryNumber, ProductId, ProductQuantity) where (OrderId, DeliveryNumber, ProductId) is PK, (OrderId, DeliveryNumber) references Deliveries and ProductId references Products

Once you did it, you can get the inventories with the following query:

select HeadQuarterId, BranchId, SupplierId, OrderDate, OrderId, DeliveryNumber, DeliveryDate, ProductId, ProductQuantity
from Branches
    join Deliveries using (BranchId)
    join Orders using (OrderId)

If orders to a branch can be made by different offices (HeadQuarters) the ordering office is no more implicitly defined by the receiving branch so the Orders table should contain one more column (OrderingHeadQuarterId) representing the new relation "Orders Made by HeadQuarters".

Answer 4

There are a few problems here, assuming you have corrected the Supplier to have just the primary key supplierId.

The most obvious is that you are missing the branch in the orders, so substitute Order.headQuartersId with Order.branchId.

Also you should be clear about the primary keys. Is the same product from two suppliers two records? Assume yes. Then the supplierId on the Delivery would be superfluous unless you want to assure that a delivery has only one supplier (you probably do). That is a valid denormalisation. Or remove the supplier from Product. Or don't, it's not a big deal.

The OrderDetailDelivery table might be superfluous if you assume one delivery per product/order. Even if you had multiple deliveries for a product/order you could accommodate it with just a deliveryId, but you may want to change the deliveries without changing the original order. In any case I don't see the need for the orderId on the OrderDetailDelivery.

Given all that, you could get a list of the orders delivered at headquarters for each of its branches thus:

                         JOIN Supplier
                       /                             
SELECT * FROM Delivery JOIN OrderDetailDelivery
             JOIN OrderDetail JOIN Product
                              \ 
                               JOIN Order JOIN Branch JOIN HQ

all along the appropriate foreign keys. If (as @alessandro points out) the ordering HQ may be different from the Branch HQ that is just extra information.

I don't know how this gets you inventory. I would think you would have to record things going out or at least going to the branch. I guess @qazifarhan gives you something like that but you could just have two nullable delivery dates for the branch and HQ. null would mean not yet delivered. Records with an HQ delivery date but no Branch delivery date would be your HQ inventory.