Hello,
I have been developing a database for my company for a couple of years now. I'm almost ready to launch the application, but I need to deal with the last crucial design issue.
The database will store PURCHASE ORDERS (which I purchase from my supplier), DELIVERIES (actual item deliveries that I receive from my supplier along with serial numbers, can be partial deliveries of purchase orders), and then almost the same thing on sales side (SALES ORDERS, SHIPMENTS).
The model already works, but I completely forgot that we also have product bundles that we need to work with, and this completely broke my current data model.
I will try to paint an example of what I mean:
tbl1Products - typical product table, if ProductTypeID = 2, that means that it's a bundle, which must consist of other non-bundle products (coded in front end)
ProductID INT NOT NULL IDENTITY(1,1) ProductTypeID INT NOT NULL
(1 = product with SN)
(2 = bundle)ProductName NVARCHAR(100) NOT NULL UNIQUE ListPriceCZK DECIMAL(10,2) NOT NULL 1 1 Standalone product A 100 2 2 Bundle X 250 3 1 Item C 150 4 1 Item D 150
tbl1BundlesProducts - bundle configuration. In this case, ProductID = 2 (Bundle X) consists of products Item C and Item D, one each. If this bundle is purchased for 250$, it's better for the customer than to pay for individual products (2x150$)
BundleID INT NOT NULL IDENTITY(1,1) ProductID INT NOT NULL Quantity DECIMAL(6,2) NOT NULL PercentageValue DECIMAL(3,2) NOT NULL 2 3 1 0.50 2 4 1 0.50
So far so good, I'm happy with this setup, it seems to work fine.
But the "problem" comes when we actually start placing orders :-).
Initially, I had this ordering design (we can stick with the purchasing side for simplicity):
tbl1PurchaseOrders
PurchaseOrderID INT NOT NULL IDENTITY(1,1) SupplierID INT NOT NULL PurchaseOrderDate DATE NOT NULL PurchaseOrderCode NVARCHAR(20) NOT NULL UNIQUE 1 1 1.1.2024 PO24001 2 1 2.1.2024 PO24002
tbl1PurchaseOrderDetails
PurchaseOrderDetailID INT NOT NULL IDENTITY(1,1) PurchaseOrderID INT NOT NULL ProductID INT NOT NULL (REF: tbl1Products) Quantity DECIMAL(6,2) NOT NULL ListPrice DECIMAL(10,2) NOT NULL QuantityDelivered DECIMAL(6,2) NOT NULL QuantityInvoiced(6,2) NOT NULL 1 1 1 1 100 0 0 2 1 2 1 250 0 0 3 2 4 1 150 0 0
Then off to actually delivering purchase orders:
tbl1Deliveries
DeliveryID INT NOT NULL IDENTITY(1,1) SupplierID INT NOT NULL ShippingMethodID INT NOT NULL DeliveryCode NVARCHAR(15) NOT NULL DateShipped DATE NULL DateReceived DATE NULL 1 1 1 DEL240001 5.1.2024
tbl1DeliveryDetails
DeliveryDetailID INT NOT NULL IDENTITY(1,1) DeliveryID INT NOT NULL (REF: tbl1Deliveries) PurchaseOrderDetailID INT NOT NULL (REF: tbl1PurchaseOrderDetails) Quantity DECIMAL(6,2) 1 1 1 1 2 1 3 1 3 1 2 (DOESNT WORK, BUNDLE CONSISTING OF MULTIPLE ITEMS) 1
This is where I start having problems when dealing with bundles, because PurchaseOrderDetailID = 2 is a bundle, but that bundle consists of Products ID 3 and 4 (1 piece each).
I basically need to store bundles in the purchase orders, but immediately split them and work with them separately on deliveries and invoices. Why? Because my supplier can have product 3 on stock, and he doesn't necessarily have product 4. So he could ship a partial delivery of the bundle.
I can think of 2 approaches and I would like your opinion about it:
APPROACH 1:
Keep this design and add another table: tbl1PurchaseOrderDetailBundleItems
PurchaseOrderDetailID ProductID Quantity UnitPrice QtyDelivered QtyInvoiced 2 3 1 125 0 0 2 4 1 125 0 0
Then in tbl1DeliveryDetails, I would change it like this:
tbl1DeliveryDetails
DeliveryDetailID INT NOT NULL IDENTITY(1,1) DeliveryID INT NOT NULL (REF: tbl1Deliveries) PurchaseOrderDetailID INT NOT NULL (REF: tbl1PurchaseOrderDetails) ProductID INT NOT NULL (REF: tbl1Products) Quantity DECIMAL(6,2) 1 1 1 1 1 2 1 3 3 1 3 1 2 (first bundle item) 3 1 4 1 2 (second bundle item) 4 1
But honestly, this looks like a crappy database design from my perspective. It was a first "working" idea that I had about this...
APPROACH 2:
Split the purchase order design to 3 tables:
tbl1PurchaseOrders
PurchaseOrderID INT NOT NULL IDENTITY(1,1) SupplierID INT NOT NULL PurchaseOrderDate DATE NOT NULL PurchaseOrderCode NVARCHAR(20) NOT NULL UNIQUE 1 1 1.1.2024 PO24001 2 1 2.1.2024 PO24002
tbl1PurchaseOrderDetails
PurchaseOrderDetailID INT NOT NULL IDENTITY(1,1) PurchaseOrderID INT NOT NULL CurrencyID INT NOT NULL VATRateID INT NOT NULL Discount DECIMAL(3,2) NOT NULL DEFAULT 0 1 1 1 1 0 2 1 1 1 0 3 2 1 1 0
tbl1PurchaseOrderItems
PurchaseOrderItemID INT NOT NULL IDENTITY(1,1) PurchaseOrderDetailID INT NOT NULL ProductID INT NOT NULL Quantity DECIMAL(6,2) NOT NULL QuantityDelivered DECIMAL(6,2) NOT NULL QuantityInvoiced DECIMAL(6,2) NOT NULL 1 1 1 1 0 0 2 2 3 1 0 0 3 2 4 1 0 0 4 3 4 1 0 0
So, if it's a standalone product, there would be 1 item record for each details record (assuming by using triggers on Microsoft SQL Server), and more items for a bundled order detail.
Then it would actually be quite easy to deliver items, because I would deal only with items, regardless of if they are part of a bundle or not.
I currently think that APPROACH 2 might actually work. Can you please comment if this is a legit and used database design, or could you please give me other ideas about how to deal with this database problem?
Thank you very much in advance!
Tomas