Database design for products with bundles of products

database-designfilemakerrelations

I am building a database system for my retail business. I have set some tables which are:

  • Product
  • Purchase
  • Sales
  • Balance

All are connected one another and are able to show my inventory level.

The problem I am having is I also sell bundles of products – that have different prices than their individual prices.
Example: I sell an orange for $1, an apple for $1.2; I sell fruit package 1 (2 oranges and 2 apples) for $3.8, package 2 (4 oranges and 4 apples) for $7.

Is there a right way how to create relationship for these product bundles?

PS: I am using FileMaker Pro creating this.

Best Answer

The pattern you are describing is often called a "parts explosion" or "bill of materials." It is part of the graphs and trees portion in the study of data structures. The essence of the solution is to realize that any given "product" can be made up of other "products." The design is then a network structure where there is a Product table that has a row for each product - whether it is made up of other products or not, and then a Product Component table that has a row for each product that is made up of other products and each corresponding product that is a component of that product. In your case, each product has a price. So you would have something like this

Product
-----------------------------------
|Name             |Price          |
-----------------------------------
|Orange           |1             |
|Apple            |1.20          |
|Fruit Package    |3.80          |
-----------------------------------

Product Component
----------------------------------------------------------
|Product               |Contains                |Quantity|
----------------------------------------------------------
|Fruit Package         |Orange                  |2       |
|Fruit Package         |Apple                   |2       |
----------------------------------------------------------

This design is preferable to a single table with a recursive association as it cleanly separates what are really two entity types - nodes and links. In our case, the products are the nodes, and the product components are the links.

While the network design is a common structure, querying it is problematic as when completely filled it is a recursive structure of varying depth. Industrial strength DBMS' such as Oracle and SQL Server have special language elements (Oracle's CONNECT BY and SQL Server's recursive CTE) to aid in making the query declarative. Given you are using File Maker Pro, which I know little about, you may not have such language constructs to help and may have to write procedure code to traverse the network. This issue can be eased however if the network turns out to be of fixed depth - say every product has either no components, or one level of components. Here are some references with regard to network structures in database design:

  1. Practical Issues in Database Management - Fabian Pascal. Chapter 7 provides the best and most understandable explanation I have found.
  2. Joe Celko's Trees and Hierarchies in SQL for Smarties, Second Edition. This is an entire book on the topic specific to the SQL standard.
  3. Enterprise Model Patterns - David Hay. A book about patterns common to all organizations (unfortunately the ER Diagrams are presented in UML but that can be overcome) there are several examples of network structures.
Related Question