Request for Estimate and Items (as either Components or Composites)

[dmontano]

I'm reaching out for some help on how to model my data for a particular area of my business. The business is a printing company that makes a wide variety of items. For example: brochures, catalogs, posters, etc. 

These items my printing company sells have many attributes that need to be entered in order to fully describe them. Attributes such as: trim size; folded size; paper material; ink configuration; bindery operations, etc. All of these items are build-to-order for customers. 

A typical business/process/document flow is as follows:

1. A Customer (or prospective customer) requests a price from us and they will also provide enough information about the printed item so we can provide an Estimate to them. The customer's initial request for pricing is documented in a document we call a "Request for Estimate". This Request for Estimate provides the basis for us to determine how it will be printed, what the time and materials are required to print the item, etc. It is important to point out that the price for these items does not exist yet: we have to build the Estimate at a later stage. In order to build an Estimate we must document what is being requested and by whom: that is the function of the Request for Estimate.

It is common for a customer to request multiple Items at the Request for Estimate stage. For example, a customer may request pricing for 5,000 brochures and 10,000 catalogs: two different Items that I need to provide pricing for, but first need to capture all specifications of these two Items.

I am building the following tables to accommodate the Request for Estimate stage:

1. Request for Estimate
2. Request for Estimate Item
3. Item

Here is some sample data showing just a few attributes in each table I am planning on building and how these three tables would be related:

1. REQUEST FOR ESTIMATE (table)

•     Primary Key: request_for_estimate_id_100
•     Customer Name: John Doe
•     Date of Request: 04/28/2024

2. REQUEST FOR ESTIMATE ITEM (table)

•     Foreign Key: request_for_estimate_id_100
•     Foreign Key: item_id_500
•     Primary Key: request_for_estimate_item_id_200

 

•     Foreign Key: request_for_estimate_id_100
•     Foreign Key: item_id_501
•     Primary Key: request_for_estimate_item_id_201

3. ITEM (table)

•     Primary Key: item_id_500
•     Item Type: Brochure
•     Item Name: ABC 123 Brochure
•     Item Paper Material: 100# White Gloss Book
•     Item Final Folded Size: 8.5" x 11"

 

•     Primary Key: item_id_501
•     Item Type: Catalog
•     Item Name: XYZ Catalog
•     Item Final Folded Size: 7" x 10"

I believe the above table build and relations shown as foreign keys is correct, but not able to handle what I present below:

My quandary is this:
The Item table represents items that can be printed — a brochure, a catalog, a poster. Some of these items I must consider as either a "component" item or a "composite" item.

A simple brochure I consider a "composite" item as it's not manufactured with multiple "components". This is a simple and straight-forward item and can be specified with only one Item record. Both its manufacture and its presentation to the customer appears as a "composite" item. One Item specification satisfies both. 

However, some Items, like a catalog are manufactured with two "components" (a COVER and INTERIOR PAGES) each which need to be specified differently. These two "components" of a "catalog" have different manufacturing sizes, ink configurations, paper materials, etc., but, ultimately need to be presented to the customer on the subsequent Estimate as one "composite" item.

The only two routes of building the table structures and relations to accommodate this that I can think of are:
Option 1: 
A single table called ITEM

• A foreign key field in this table to relate an Item to an Item, where the "components" are always a child of the "composite" item which would be the parent record.
• A field to distinguish an Item as either "component" or "composite".

Option 2:
One table for ITEM COMPOSITE

One table for ITEM COMPONENT
This table would have a foreign key field to establish the parent ITEM COMPOSITE relation.

I suspect the preferable choice is option 1; but I can't get option 2 out of my mind. Apologies for the long-text post, I am trying to be thorough and clear: both of which I may be failing at a bit. Any help appreciated.
 

[Søren Dyhr]

 

[dmontano]

Thanks Soren for the video link. It certainly gets into aspects above my current comfort level. 

So, it appears that I am dealing with a need for a Bill of Materials or a "composing" table to allow me to assemble ITEMs into ITEMs. 

A brochure is often a made as a single item (BROCHURE).

Othertimes, a BROCHURE is composed of two separate items (BROCHURE COVER and BROCHURE INTERIOR PAGES). 

Does the below structure sound like a good choice?

ITEM (table)

• item_id_pk

ITEM COMPOSITION (table like a Bill of Material)

• item_id_fk (parent)
• item_id_fk (child)

Edited April 30 by dmontano
spelling

[comment]

Will any of your items have more than one level of components? IOW, is it possible for a component to have subcomponents? 

I believe that will determine whether you can get away with a relatively simple solution or not.

 

[Søren Dyhr]

2 hours ago, dmontano said:

Thanks Soren for the video link. It certainly gets into aspects above my current comfort level. 

Perhaps this is better?: https://filemakerhacks.com/2019/03/27/bill-of-materials/

But I should mean, that this ExecuteSQL:

Quote

Let(
   [
      x =
      ExecuteSQL(
         "
SELECT parentID
FROM \"BoM\"  
WHERE childID = ? ";
         "";
         "";
         Item::pk
      );
      $count = ValueCount( x )
   ];
   x
)

Could be made by the "List of" summary function, and over a unequal related selfjoin and this trick:

https://filemakerhacks.com/2015/02/02/summary-list-as-relational-predicate-part-2/ 

This way:

--sd

Untitled.fmp12

Edited April 29 by Søren Dyhr

[dmontano]

4 hours ago, comment said:

Will any of your items have more than one level of components? IOW, is it possible for a component to have subcomponents?

Yes.

I have a magazine I did for three years that was composed as such:

Magazine (ITEM)

Magazine Cover (ITEM)

Magazine Interior Pages (ITEM)

Promotional Envelope Tip-In  (ITEM) a envelope with removable glue applied to adhere to one of the Magazine Interior Pages.

Promotional Coupons (ITEM) inserted into the Promotional Envelope.

 

In estimating, we will produce all FOUR of the "children" items in separate processes, materials, quantities, etc. Then they will be assembled into the last ITEM — the magazine.

Of interesting note: the first ITEM (magazine) specification is really an amalgamation of all of the children specifications. Likewise, the estimation of materials and labor for the parent ITEM magazine are derived from the sum of all of the children, plus a few operations specific to it. I share this view as to help illuminate what is possible with an ITEM.

 

4 hours ago, Søren Dyhr said:

Perhaps this is better?: https://filemakerhacks.com/2019/03/27/bill-of-materials/

I was just reading through this article when I got tore away with other tasks today! lol. Thank you for your help. I have lots to chew on to grasp.