Advance relations challenge

Ask for Help
1

Hello everyone!
I’m building my first app, and this is my biggest challenge…
The app is a management system for bulk order quantities of products.
The system only serves as an information record for my users,
They upload information about the orders, suppliers, prices etc… just to save their info.

I have:

  • Products
  • Suppliers
  • Orders

Here’s the challenge:
Each product has it’s price per unit, but the price depends on the order’s quantity.
Example:
Let’s say the user opens an order #1, with:
100 pieces of “soccer ball model A” that costs 2.8$ per unit
2000 pieces of “uniforms model Z” that costs cost 2.2$ per unit

And order #2, with:
1000 pieces of (the same) “soccer ball model A”, this time it costs 1.5$
4000 pieces of (the same) “uniforms model Z” this time it costs 1$

So, the price depends on the order’s quantity.

How would you relate the price to the product and the quantity and the order all together?

Thank you!

2

If I understand correctly, you are asking for a calculation of the Price Per Unit, Right?
The Total Price is calculated as:

Total Price = Quantity * Price Per Unit

And the Price Per Unit depends on certain conditions, which we need to define.

You can use an If-Then-Else column in the Products table to dynamically set the Price Per Unit based on conditions like quantity. Here is how you can handle it:

  1. Create an If-Then-Else column in the Products table (or better, in Orders, since prices are specific to each order).
    • If the Quantity (linked from the order) is below 1000, then set the price to 2.8$.
    • Else, set the price to 1.5$.
    • e.g.: If Quantity < 1000, then 2.8$. Else 1.5$.
  2. Calculate the Total Price:
  • Add a Math column:
Total Price = Quantity * Price Per Unit
3

How is your data structured? Do you have multiple rows for each product with different quantity/price, or are all the price combinations in the same product row?

1 Like
4

Hey!
Thank you for your time.
I’m not asking for the calculation of price per unit.
I’m wondering where (In what table) should I store the data, and how to relate the product & the price & the quantity.

The complexity is that the same product has different quantity and price in each order.

So, where should I save the data? In which table?

I thought about letting them duplicate the same product.

So for example:
“Model A” in 100 pieces will have a price of 2.8$
And “Model A” in 200 pieces will have a price of 2.5$

They’ll have “model A” in quantity of 100 in a price of 2.8$
And if they want to order the same product again, but this time 200pcs, they’ll have to duplicate “model A” and update the new price (cause 100 pcs don’t cost the same like 200pcs).

**It’s NOT a ecommerce store
It’s just for my clients to STORE their date

What did they order, in how many pieces, at what price.

5

Right now I have 3 tables:
Products
Orders
Supplier

I thought about creating another table,
that will store the different scenarios… maybe this way i can relate the product, price and quantity together?

image
image1920×607 81.8 KB

6

That just means you need to store both the quantity and price per unit with each order.
Can I assume there could be multiple line items for each order?

Assuming that’s the case, you could split orders up into two tables. Firstly, an Orders table which would have the following columns:

  • RowID (OrderID)
  • Date
  • SupplierID

And then an Order Items table with the following columns:

  • OrderID (link to the parent order)
  • ProductID
  • Quantity
  • Unit Price
2 Likes
7

That is a GREAT idea!
I’ll implement it.

8

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.