I have recently created a solution to manage inventory for our business. I remember when I was building another solution that we heavily rely on these days, the advice I received on this forum was a wealth. We were in need of a special type of relationship and the Volunteer Community Leader suggested some things that were very helpful. In short he gave the idea about a relationship type to use with the solution, and that was the reason it succeeded. To date the solution helps us so much. At the time this was very much over my head. However it pushed me to learn and that was best part. It’s because of this advice that I have the skills now to attempt relationships for the current solution I am referring to. So I wanted to also post my new design here hoping to get the same helpful eyes of the community and the Volunteer Community Leader. This way I will feel that I have made every effort to start from a place of best practice, and like I said, the advice here has helped me learn so much.
The main area where I think I need to look at, is the QUANTITY:: table. The way I imagine it is that, I will have each ITEM:: record (because they all have a quantity 1 or more) have a records in the QUANTITY:: table matching it’s _Pk (Primary Key). So if the quantity of a Flashlight is 20, there would be 20 QUANTITY:: records for that ITEM::_Pk. I figured it could easily be manipulated, via scripts to update the related records when an ITEM:: record has a change in quantity. We also have AUCTIONS:: this is because we sometimes put unused items on eBay. I figured that we could always be referring to the first record of an ITEM::. One thing I was wondering is would it make better sense to have one QUANTITY:: record for each ITEM:: record and just have a count, but it seemed like a better and more versatile by having them individual. Because QUANTITY:: records help us store them in our LOCATION:: table, I find individual better if some are stored in different places. Is there something obvious I am overlooking? If there is some better way of a relationship to be setup in this type of solutions I welcome the suggestion. Thanks