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