I am developing a webapp for my business, a mobile phone repair shop. The problem I have is I don't know how exactly to implement a way to manage the parts inventory.

Here what i have now:

{Parts(id, name)} 
{Inventory(id, part_id, initial_qty, current_qty, initial_price, current_price)} 
{Operations(id, name)} 
{Inventory_Operation(id, inventory_id, operation_id, explanation, qty, price)}

I need to have a possibility to add inventory, sell inventory, add inventory to repairs, mark inventory as bad, return inventory.

I am developing using MySQL.



As i mentioned before, the app will manage all of the shop repairs, i have the rest figured out, but this inventory management part of the database i cannot get a clear image. When i buy parts from suppliers i need to add them to the inventory (they dont have serial numbers, i only need the quantity), then when i do a repair, and use a part, then the inventory for that part must be decreased, sometimes after a repair the client comes back because the part is not ok, and i need to offer warranty so i need to mark that part as defective in the inventory, other times i do the repair and the client declines to pay so i must return the part to the inventory. The other problem is, i can receive the same part with different price, or different supplier.

LE 2 I have made the whole app db diagram, the diagram is more or less accurate. enter image description here

  • It's hard to suggest a database design based on the information you gave. It would help if you further described the entities that you want to store data for, as well as how they relate to one another. – Joe Borysko May 13 '15 at 20:07
  • This question has a good example of a thorough project description in the italicized text: dba.stackexchange.com/q/99363/64482 – Joe Borysko May 13 '15 at 20:14
  • @JoeBorysko added some more information, hope it helps. – Dacian B. May 13 '15 at 21:15
  • You mentioned that you don't have serial numbers for individual parts, and that you only need the quantity. If you need to mark a particular part as defective though, you would need a way to reference that part. Are you sure there's no unique number you can use to identify an individual part? – Joe Borysko May 13 '15 at 22:48
  • Also, are you asking for suggestions for the database model, or are you only trying to understand your current model? – Joe Borysko May 13 '15 at 22:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.