Order database model with product bundles

[Thomasso]

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

[ArviLaanemets]

My advice is to have purchase orders table like you have, and the purchase order items table like:
tblPurchaseOrderItems: PurchaseOrderItemID, PurchaseOrderID, ItemID, BundleItemID, CurrencyID, UnitPrice, VATRateID, QuantityOrdered, QuantityRecieved, ...;
, and to have a tables, where you define specific bundles and bundle items, along with discounts for items in bundle. Like:
tblBundles: BundleID, SomeComment;
tblBundleItems: ByndleItemID, BundelID, ItemID, ItemDiscount (I added discount here, but in case it is same for all bundle items, you can have it in tblBundles instead)

Your tblPurchaseOrderDetails looks for me as abundant.

In tblPurchaseOrderItems, for same order can be any number of rows for same item. E.g a row with empty BundleItemID for specific item not belonging to any bundle, a row for same item with BundleItemID = SomeValue, a row for same item again with BundleItemID = SomeOtherValue, etc.

Item's unit prices must be probably read from some SupplierItems table, currencies from Suppliers table, and VAT rates must be somehow linked to Items table (when different VAT rates are used in country, they are usually set for certain item groups).

Any monetary amounts for order must be calculated based on tblPurchaseOrderItems.

[orange]

Thomasso,

I find this statement hard to understand "I'm almost ready to launch the application". Since you are still trying to design a data structure to support you business requirement, I think you still have "work to do". As for your approaches and which may be suited to your needs, why not take some test data and some test scenarios and "test/vet" your approaches/model. It is much easier and efficient to test and confirm your logic at the model stage than after you have constructed code/programs.

It might be helpful to you and readers to list your requirements with simple examples and test each of your product types/bundles/items against each approach. You may find this link helpful.

[Thomasso]

Thomasso,

I find this statement hard to understand "I'm almost ready to launch the application". Since you are still trying to design a data structure to support you business requirement, I think you still have "work to do". As for your approaches and which may be suited to your needs, why not take some test data and some test scenarios and "test/vet" your approaches/model. It is much easier and efficient to test and confirm your logic at the model stage than after you have constructed code/programs.

It might be helpful to you and readers to list your requirements with simple examples and test each of your product types/bundles/items against each approach. You may find this link helpful.

I am attaching a few generic data models that came from Barry Williams' site(no longer active). They do not address your specific situation, but they are related to Customers/Suppliers/Products/Orders and Deliveries.

I understand that it sounds strange like this, but the reality is that I only described an isolated issue in an almost complete software. I already have a robust data model in place, the SQL script is more than 5 000 lines (tables, views, stored procedures, functions, triggers...), VBA front end is 50+ forms, thousands of VBA code lines...
I only need to incorporate product bundles, which really is just a modification of couple of tables, views... I spent 3 years developing it, so I am confident to say that "I'm almost ready to launch"

ArviLaanemets

My advice is to have purchase orders table like you have, and the purchase order items table like:
tblPurchaseOrderItems: PurchaseOrderItemID, PurchaseOrderID, ItemID, BundleItemID, CurrencyID, UnitPrice, VATRateID, QuantityOrdered, QuantityRecieved, ...;
, and to have a tables, where you define specific bundles and bundle items, along with discounts for items in bundle. Like:
tblBundles: BundleID, SomeComment;
tblBundleItems: ByndleItemID, BundelID, ItemID, ItemDiscount (I added discount here, but in case it is same for all bundle items, you can have it in tblBundles instead)

Your tblPurchaseOrderDetails looks for me as abundant.

In tblPurchaseOrderItems, for same order can be any number of rows for same item. E.g a row with empty BundleItemID for specific item not belonging to any bundle, a row for same item with BundleItemID = SomeValue, a row for same item again with BundleItemID = SomeOtherValue, etc.

Item's unit prices must be probably read from some SupplierItems table, currencies from Suppliers table, and VAT rates must be somehow linked to Items table (when different VAT rates are used in country, they are usually set for certain item groups).

Any monetary amounts for order must be calculated based on tblPurchaseOrderItems.

Yes, that's actually right that the 3rd table is abundant. Thank you for helping me realize that.

So, in this model, would you recommend that if a user tries to add a "Bundle" in a Purchase Order, there would be an INSTEAD OF INSERT trigger, and the respective items with "BundleID" would be added instead?
Then I assume I would group the items into the bundle name on reports (for example Purchase Order Confirmation).

Would that work in your opinion or would you recommend something else?

Thank you very much!

Tomas

[ArviLaanemets]

I can advice something only 'on fly', as the logic here may get very complicated very fast.

At start, what do you mean with 'trigger'. I hope you don't plan to insert data directly into tables!

I think there are really 2 ways to enter order items info:
a)
You have a main (single) form where you select/register a purchase order;
In this form you have a continuous subform, where order items rows are displayed (and where you can add/delete them). The main form and subform are linked by field PurchaseOrderID. At start you enter the row for item without determining any bundle info. The quantity you determine for this row will be the base quantity for this item. Now when you enter for this item another row, and determine there the BundleID, and the quantity for this bundle, you must have some event running, which will check, is there enough same items without bundle determined (free items), and is there enough other free items determined for this bundle. When not, the error message is displayed. When yes, then quantities of all bundle components are removed from according free items, new rows are added for every bundle component which didn't have it before, and item quantities are updated for every bundle component row.
b)
You have a main (single) form where you select/register a purchase order;
In this form you have a continuous subform, where order items rows are displayed (a single row per item). The source of this form will be an additional table. For every item in subform, the total quantity is determined (no bundle info here);
On main form, you have another continous subform, where bundles, and their quantities, the item currently active in 1st subform belongs into are displayed (for this, you need a hidden unbound text box in main form, where OnCurrent event of 1st subform writes the value of current ID in 1st subform. The 2nd subform will be linked to this unbound control. In header of 2nd subform, you'll have a command button, which will update the purchase order items table with data. You can have a 3rd subform which displays those rows, but this must be a read-only info.

[Thomasso]

I can advice something only 'on fly', as the logic here may get very complicated very fast.

At start, what do you mean with 'trigger'. I hope you don't plan to insert data directly into tables!

I think there are really 2 ways to enter order items info:
a)
You have a main (single) form where you select/register a purchase order;
In this form you have a continuous subform, where order items rows are displayed (and where you can add/delete them). The main form and subform are linked by field PurchaseOrderID. At start you enter the row for item without determining any bundle info. The quantity you determine for this row will be the base quantity for this item. Now when you enter for this item another row, and determine there the BundleID, and the quantity for this bundle, you must have some event running, which will check, is there enough same items without bundle determined (free items), and is there enough other free items determined for this bundle. When not, the error message is displayed. When yes, then quantities of all bundle components are removed from according free items, new rows are added for every bundle component which didn't have it before, and item quantities are updated for every bundle component row.
b)
You have a main (single) form where you select/register a purchase order;
In this form you have a continuous subform, where order items rows are displayed (a single row per item). The source of this form will be an additional table. For every item in subform, the total quantity is determined (no bundle info here);
On main form, you have another continous subform, where bundles, and their quantities, the item currently active in 1st subform belongs into are displayed (for this, you need a hidden unbound text box in main form, where OnCurrent event of 1st subform writes the value of current ID in 1st subform. The 2nd subform will be linked to this unbound control. In header of 2nd subform, you'll have a command button, which will update the purchase order items table with data. You can have a 3rd subform which displays those rows, but this must be a read-only info.

I forgot to mention that the back end is running on Microsoft SQL Server. It features "triggers". INSTEAD OF INSERT trigger means that when a row is to be inserted, the trigger fires, checks for conditions and if they apply, it doesn't insert the row, but does something else "instead". So in this case, if a user enters a bundle product into an order, the trigger would check if this is a bundle, and if it is, it wouldn't insert this one, but instead if would insert the bundle items, and assign them the BundleID foreign key. I think this is kind of common scenario in Microsoft SQL Server.

I don't think you and I have the same understanding of our bundles. For me, a bundle is a predetermined package of products with certain quantities and a total price for the bundle. I don't want users to create their own bundles while entering a purchase order. I want to define bundles and allow users to add these bundles to purchase orders.

[dblife]

I appreciate you have a lot of records, code etc but it is possible to add a many-to-many table where every product a customer orders is a 'bundle' as you put it.
A bundle could be one product, three products, 1200 products.
The customer orders from the 'bundle' table rather than ordering from a product table OR a bundle table.
As a simple example,
A customer orders a banana from a bundle table and the only product in the m2m table is 1x banana.
If they order a bunch of bananas (a bundle) the record in the m2m table specifies 5x bananas against 'bunch of bananas'
If they order 'fruit basket' (bundle) they get three records worth of products, 5x bananas, 3x apples and 2x oranges... everything is a bundle.
Each invoiced item is grouped as the bundle - this can be done by using the 'grouping' function in report design in Access.