Is there a better solution?

[NCD]

I have a;

•Product table

•Size table (this has a Product ref column)

•Recipe table (this has a Product ref, Size ref, & Material ref column)

•Material table

Ideally, I want the Product to hold all the Recipe for all Sizes related to that Product.

I am currently doing so by having both Product and Size table to be referenced in the Recipe Table, but I am hoping some body  on here might have a better solution?

The purpose for this is because some materials are particular to a certain size, some are for some sizes, and some are for ALL sizes.

[Kirk_Masden]

I haven't made the kind of app you are working with but if I were to try, I might put all of the sizes for a particular item in a single column as a list.  It could be something like "Small , Medium , Large" for one item and "Small , Medium , Large , Extra Large" for another.  Then, these could be split and presented as an enum to the consumer.  Personally, I can't see the advantage of putting the sizes in a separate table.

I'll follow this thread to see what others say.  As I wrote above, I've never built this exact type of app.

[TeeSee1]

Without knowing the exact relations between all the entities you have listed, my guess would be

PRODUCTS: p_id (key), size_id(ref)

SIZES: size_id(key)

MATERIAL: mat_id (key)

RECIPES:  rec_id(key), p_id(ref)

RECIPE_DETAILS: rec_det_id(key), rec_id(ref, is_part_of), mat_id(ref)

This way you have in your PRODUCTS table all the RECIPE/DETAIL info via [related recipes][mat_id]

What you have indicates that a given size, a product is identified.

I would guess it is usually the opposite. Given a product, then its size is identified.

[NCD]

Hi @TeeSee1 

I understand it's "usually the opposite".

The size_id(ref) should be in PRODUCTS Table.

But the size isn't just to store the sizes but rather the production amount of per size of that product so in my case,  

"a given size, a product is identified." is correct I think.

So I when I look at in a detail view,

[PRODUCT A]

Related sizes:

Small | Production amount 20 

Medium | Production amount 40 

Large | Production amount 40 

Related Recipes:

Material 1 | for size S | [Small Size Production Amount]*[Minimum Order Quantity of Material] = Required amount 

Material 2 | for size M & L | [Medium & Large Size Production Amount]*[Minimum Order Quantity of Material] = Required amount 

Material 3 | for ALL sizes | S, M & L Size Production Amount]*[Minimum Order Quantity of Material] = Required amount 

[NCD]

My Relations are;

PRODUCTS: p_id (key), 

SIZES: size_id(key), p_id(ref)

MATERIAL: mat_id (key)

RECIPES:  rec_id(key), p_id(ref), size_id(ref), mat_id(ref)

[NCD]

Made a sample app to explain myself better.

https://www.appsheet.com/templates/Demo-App-for-my-question?appGuidString=336042a2-c2cd-4882-b44c-f0... 

If you could please take a look in your spare time.

What I have now works, for the most part. It's just that I can't help but wonder if there might be a better way to go about this.

Just feels weird that I have to reference both Size and Product in the Recipe Table and have to have a blank cell in order for it to consider for all sizes.

Ideally I want the Recipe table to reference just the Product and Material table, but have the same way effect as I have now.

[TeeSee1]

My understanding of your requirements and my comments...

SIZE is essentially a production order which specifies a product, its Size and a qty to be produced.

RECIPES calculates the qty (=MOQ * production qty in SIZE) of materials needed to execute a SIZE order.

So RECIPES have to reference SIZE table in order to retrieve the production qty. There is no way around it. (There is no way to get to the production qty via PRODUCS and MATERIALS).

As for the PRODUCTS reference in RECIPES, you can remove it because the PRODUCT to produce is available in the SIZE row which you have to access anyway.

You currently specify the materials needed directly in RECIPE rows. I would create a 'validation' table containing permissible PRODUCT-SIZE-MATERIAL combinations to make sure you do not enter incorrect materials.

I do not understand where you mention that you "have to have a blank cell in order for it to consider for all sizes". In your Demo, for Product A, I assume that Material 3 is calculated based on the sum of the production qty's for all sizes because M3 is required for all of them. I would just compute M3 for each SIZES because you mentioned some are required for some sizes (not all) and need to enter RECIPE for each one anyway.

I also noticed that when you create SIZE's (production orders), they are not related to each other and wondered how they are bundled together to calculate the total production qty. My guess is you are creating a 'calculator' app and not really a production order system?

More often than not it would help to share as much context - background and use cases - as possible to make it easy for community members to understand what you are trying to do without having to guess so much.

I hope this post helps you a bit.

[NCD]

@TeeSee1

First of all thank you for your time that you took to look into my sample app and your feedback.

My guess is you are creating a 'calculator' app and not really a production order system?

You are kind of correct. It is “not yet” a production order system. At this moment, it is still just an app to store & manage;

• How many sizes are created for that specific product
• The qty. of production per size of that specific product 
• What kind of materials are used for that specific product
• What kinds of materials are available to use, and the MOQ and unit price (which I forgot to include in the Material Table)

and from this data, it’s able to calculate;

• How much materials are needed to manufacture a specific product
• The total cost per material
• Total cost per product

People in my company will take a look at these numbers and if they are satisfied, only then they will proceed to make an order of materials.

So RECIPES have to reference SIZE table in order to retrieve the production qty. There is no way around it. (There is no way to get to the production qty via PRODUCTS and MATERIALS).

As for the PRODUCTS reference in RECIPES, you can remove it because the PRODUCT to produce is available in the SIZE row which you have to access anyway.

Going back to this part of your post, this is where I’m kind of stuck? I guess.

So as an app maker probably the best way to go about it, is to remove the PRODUCTS reference in the RECIPES table and stick with just the SIZE reference in the RECIPES table.

But as an app user and as a UX POV, it’s kind of tedious to 

[go into a Product]→[go into a size]→[add/fill in recipe for each size]  

and also hard to look at the whole picture.

So what I did was, as you recommended;

• PRODUCTS reference (is_part_of) in the SIZE table
• SIZE reference (is_part_of) in the RECIPES table

So that when deleting, it would go through proper route to delete,

but for an easier overview for users, I have created;

• PRODUCTS reference in the RECIPES table

This way, just by going into the PRODUCTS detail view, you are able to 

• See the whole recipe for that product
• add a material and select the proper size to create recipe

I’m not fully sure what you meant by;

You currently specify the materials needed directly in RECIPE rows. I would create a 'validation' table containing permissible PRODUCT-SIZE-MATERIAL combinations to make sure you do not enter incorrect materials.

But I’m guessing it’s about what I’ve mentioned above;

• add a material and select the proper size to create recipe

In my company I think this validation can be handled by the employees themselves without error.

This might be something I have to think about but not really a priority issue. Thank you for your concern.

I also noticed that when you create SIZE's (production orders), they are not related to each other and wondered how they are bundled together to calculate the total production qty. My guess is you are creating a 'calculator' app and not really a production order system?

Going back to this part, SIZE doesn’t need to be related to each other nor to SIZEs of other PRODUCTS. As long as each SIZE is related to their parent Product, I can get the total production qty. by SELECT and SUM the qty. of each size.

For my specific use case, and what my company wants, I think I am on the right track. (maybe not but I have to keep moving, and if something goes wrong I’d have to come back)

One last question though.

My next move would be to create a PURCHASE ORDER for those materials in the RECIPE table, not by each material but by each vendor.

Taking the sample app as an example;

• Material 1 & Material 2 = Same Vendor (Vendor 1)
• Material 3 = Different Vendor (Vendor 2)

Product A needs;

20 of Material 1

200 of Material 2

300 of Material 3

Product B needs;

40 of Material 1

250 of Material 2

450 of Material 3

So I need to make a total purchase order to;

Vendor 1

60 of Material 1

450 of Material 2

Vendor 2

750 of Material 3

Are there any sample apps or templates that might lead me to the right path? 

Again, thank you very much for your time!

[TeeSee1 · Accepted solution]

I think I understand your requirements better.

There are a few ways to improve usability.

Here is one way. Instead of having everything in the Products Detail View, you could display SIZES and RECIPES views in the dashboard. (I have a VC in recipe to get an associated PRODUCT ID but not really necessary. Also did not enter some recipe No.. not set to mandatory and tend to forget.)

Yes, the validation table is not mandatory, but people make errors and it could save a lot of time in the end. And it is a good practice when you create an app.

As for creating purchase orders, many ways to do this. So you have to think how your purchasing department actually does the work.

I first maintain a preferred vendor in the material table. 

Then a PO table where you have Vendor, PO Date, etc. and a PO Details (child) to hold one material per row with required qty. The qty can be auto populated based on the expression it its App Formula. I modified your demo app assuming that you are processing all the PRDUCTS/SIZES/RECIPES together at once.

Hope this gives you a starting point.

Here is the link to the modified app.

https://www.appsheet.com/portfolio/3401559

AppSheet Demo - Mod

[NCD]

@TeeSee1 

Thank you very much, This helped me a lot with what I am looking for!!