-1

I had some sort of IT backgrounds with less-more economic and management understandings and some common senses. I added some functionality to current web based system which used to control quantities of inventory over fluctuation cost.

The business focused on reseller distributed consumption items from different spots of suppliers. Sometimes, the business retrieved similar actual items (same of type of brands, packages, and functions) coincidentally. Sometimes, the cost the business paid weren't similar because different suppliers earned their own profits.

As web administrator perspectives, even the business bought 2 cartons of chocolate bars with same brand from 'supplier 1' and 'supplier 2'. The cost for 1 carton possibly different. That why I need to categories them into different entity/records (different supplier obviously). After all, i inserted 2 unique_id into database.

As customer perspectives who demanding to lowest price, they think chocolate bars from both carton are same. After added some fixed profits, both chocolate bars ended up to have different prices.The business preferred to sell cheaper carton of chocolate bar first but business need to concern about standarization F-I-F-O stock control managements (because consumable items have their expired date).

System that I implement, have some stock quantities control which allow to trigger sort of message to re-ordering items once quantities below than benchmark.

Case 1:
The business start re-ordering and 'supplier 1' inform there will be increasing chocolate bars price. The remaining chocolate Bars bought from 'supplier 1' help the business to earn more profit. After packages arrive to business, Web admin will update new total cost and new price of chocolate bars including quantities. This case doesn't have problems.

* Edited Case 2:*
The business start re-ordering and 'supplier 2' inform there will be reduction chocolate bars price. It isn't possible to overwrite current total cost and price from system, because the business have stocks quantities left, and make sure business won't get any loss.

After 'supplier 2''s packages arrive to business, as web admin. What should i do to resolve case 2? I want to ensure there is no duplication item inserted to system with similar supplier who supplied those item (not to insert new record). The quantities of packages arrival also should be keep tracked by owners. As you see there are more hundred various of items, it isn't possible for stock control specialist to postpone insertion records of incoming quantities to warehouse.

Added: other scenario happens, if qty in stocks left '0' at inventory similar benchmark '0' had been set before, as you know, staff authorized to edit records (change total cost even it's reduction cost than previous one), the business doesn't affect to fluctuation cost. This scenario seem to be impossible, because business won't left out of stock to 0 of their inventory, the time cost to re-order, transportation, handling time need to be compensate. Are there any alternative ways to do so?

Am I on right track to separate both chocolate bars into 2 records even they have similar brand and different suppliers?

please help me to enlighten this cases.

Thank you very much! your kindly helps, ideas and comments are highly appreciated!

Naoki
  • 1
  • 2

1 Answers1

0

Your question appears to be "how does this work in the real world" and not "what is the best way to design the database". That's the right question to be asking at the conceptual level. However, I will make one change to your title. The conceptual level isn't about design. It's about analysis. At the conceptual level, you don't invent the answers. You discover them. At the design level (logical design, physical design) you invent the answers, hopefully agreeing with what you discovered earlier, and maybe agreeing with "best practices".

The price of something sold depends on two things: the supplier and the product. Actually, in some cases it can depend on the buyer as well, where one buyer gets a discount, and another one does not. It can depend on time, because prices fluctuate over time. It can depend on what else was bought in the same order.

The best plan is to include the price in the transaction row (record), even though this seems redundant. You'll be glad you did, downstream.

Walter Mitty
  • 18,205
  • 2
  • 28
  • 58
  • The systems had been done for long months ago, we did record 'price' to our transaction table, so owners able refer transaction table (sales) back annually. We're being asked to improve system, because business having trouble to manage quantities of re-order items when re-order item costs reduced than previous costs. It very redundant to insert new record only for track qty in stock, cost and price which used for sales. What re-order item are actual same items from same suppliers with different date/time to order. That why we need to revise back our system to design. – Naoki Sep 16 '14 at 15:58