Unanswered: Problems in developing ERD for inventory system
I am going to develop a practical project on inventory system for a mobile distributor company. The whole business process of that company is given bellow:
The system that I need to develop is for a mobile distributor company named who is addressed by RDS. They receive different models of product from an AGENT who directly doing business with a mobile manufacturing company. After receiving products from AGENT RDS sells those products to different DEALERs. RDS purchase products from Agent in a rate and sell those products to the Dealers in another rate fixed by the Agent. Sometimes it may happen that after purchasing products from Agent the price may fall down. Then RDS calculates the total less amount of products which are now left in stock and send those to the Agent. For example Model 001 amount of 100ps were received in 100$ rate and Dealer rate was 110$. Now price is fall down in 10$. That means now RDS needs to sell per product in 100$ rate. So to fill up the loss RDS calculates the stock amount for that Model001 product which have been left and need to sell in 10$ price in less. Then RDS will send that total amount to the Agent for compensation and agent will compensate those amounts by that time or later. Each compensation amount has duration of 3 months. It means RDS needs to claim for within the time duration.
Another matter is DOA (Damage on Arrival) product. Sometimes Dealer returns some damaged product to RDS. RDS then return those products to agent for warranty. Agent then returns those products to RDS. But sometimes it may happen that some particular model may unavailable due to close of production. That time Agent claims to RDS an amount of money to adjust the price to return another model of product which is higher than return one.
Agent sometimes offers different bonus both to RDS and Dealers. For example if any RDS purchase 100000$ of products then they will receive 10000$ as commission. Or if any RDS buys model001 10 ps then will be given 1ps model001 as commission.
Now I am going to describe the whole process step by step. At first RDS creates a Pay Order through Bank to the agent to purchase different models of products. After receiving payment, Agent sends products with invoice to the RDS. Then RDS sell those products to dealers.
I have analysed the whole system and finds the following requirements of my proposed system, those are:
- Product details
- Customer details
- Purchase details
- Sales details
- Compensation while product price is less (TPP policy)
- Damage product return policy (DOA Policy)
- Company promotion
- Customer promotion
- Different statistical reports
I have also prepared an ERD. My table descriptions are:
Now I am confused to implement TPP and DOA. Please find me a better solution. Please also suggest me for the change of my existing ERD.