Skip to main content
Software Engineering

Return to Answer

Gave more db modeling hints since user is unlikely to be able to find a C# compatible open source order management system.
Source Link
rzrelyea
rzrelyea
  • 111
  • 2

When Supplier Bar sends/allocates N more Foo Items to the Retailer, Retailer should increase its Supply of Item Foo by N. You can also attach a "type" to the Supply that you add.

Again - don't build this all by yourself!

But if you insist, I've seen consignment modeled 2 different ways and both worked fine. One way is to model all the consigned inventory as a separate distribution center. For example, if you have 45 physical stores and 5 warehouses, you'd have 50 distribution centers. You can treat all consigned goods as being held in a separate distribution center. This works particularly well if your suppliers don't actually send you the product, and the product is in fact shipped to the consumer from the supplier.

The other way I've seen this done is to use a type attribute on your Supply. Some example types could be "good condition", "slightly damaged", "safety", or in your case, "purchased" vs. "consigned." Therefore you could have rows like the following in your Supply entity:

id  sku         quantity  type
--  ----------  --------  ---------
1   foo-item1   10        consigned
2   foo-item2   5         purchased
3   fooz-item1  20        consigned

Note that the sku must uniquely identify an item and if you have multiple suppliers for that item, then you need to be able to navigate from the sku back to the supplier.

And you can also find a reasonable open source data model for invoices and oders.

When Supplier Bar sends/allocates N more Foo Items to the Retailer, Retailer should increase its Supply of Item Foo by N.

Again - don't build this all by yourself!

When Supplier Bar sends/allocates N more Foo Items to the Retailer, Retailer should increase its Supply of Item Foo by N. You can also attach a "type" to the Supply that you add.

Again - don't build this all by yourself!

But if you insist, I've seen consignment modeled 2 different ways and both worked fine. One way is to model all the consigned inventory as a separate distribution center. For example, if you have 45 physical stores and 5 warehouses, you'd have 50 distribution centers. You can treat all consigned goods as being held in a separate distribution center. This works particularly well if your suppliers don't actually send you the product, and the product is in fact shipped to the consumer from the supplier.

The other way I've seen this done is to use a type attribute on your Supply. Some example types could be "good condition", "slightly damaged", "safety", or in your case, "purchased" vs. "consigned." Therefore you could have rows like the following in your Supply entity:

id  sku         quantity  type
--  ----------  --------  ---------
1   foo-item1   10        consigned
2   foo-item2   5         purchased
3   fooz-item1  20        consigned

Note that the sku must uniquely identify an item and if you have multiple suppliers for that item, then you need to be able to navigate from the sku back to the supplier.

And you can also find a reasonable open source data model for invoices and oders.

Source Link
rzrelyea
rzrelyea
  • 111
  • 2

Fist, I must implore you not to build this system from scratch. It is a much more complex project than it seems. Please consider using an open source eCommerce platform, order management system, and product catalog. Check out the Apache Open for Business Project. There are many others - most want you to pay them consulting fees.

Many order management software packages often model this using the concepts of supply and demand. You have some high level entities, such as Product (for example, FancyBrand Men's V-neck undershirts), Item (for example, FancyBrand Men's undershirt in size Medium and color white), Supplier (the company who sells the item to the retailer), Vendor (the company who actually makes the item, who is not necessarily the same as the Supplier). A Retailer could buy Product Foo, which is produced by Vendor Fooz, from Supplier Bar today and then from Supplier Baz tomorrow. Or Foo could by supplied by both Bar and Baz at the same time (hint: don't use 1 column for the Supplier-Product relationship).

When Supplier Bar sends/allocates N more Foo Items to the Retailer, Retailer should increase its Supply of Item Foo by N.

When a Customer begins the process of purchasing M Foo Items, you typically increase the demand against Foo by M. After the transaction is complete (credit card has been validated, fraud check has completed, etc.) Retailer decrements both Supply and Demand by M.

I would also avoid thinking about the Supplier's inventory level for Foo. The Retailer knows nothing about the Supplier's inventory. The Retailer only knows how many Foo items are in the Retailer's inventory, and of those, how many came from Supplier Bar or Supplier Baz. Even if the Retailer hasn't paid the Supplier for the item, and even if the Supplier never sends the items to the Retailer (maybe because the Supplier ships directly to the customer after the Retailer handles the transaction), if the Retailer can sell those items to its customers, then those items are part of the Retailer's Supply.

There are 2 ways a Supplier might want to be paid: on each transaction, or periodically. If the Supplier wants to be paid every X days for whatever items the Retailer has sold. So you need to be able to answer the question: "How many Foo items from Supplier Baz did I sell in the last X days?" If the Supplier wants to be paid on each transaction, you still need to know which Foo Item in each transaction was sent/allocated from Supplier Baz.

Again - don't build this all by yourself!