sql - How to create db table structure to capture and process items used in food-bank deliveries/orders

Question

This is a business process driven question rather than a code specific one. I’m learning to use SQL to help support a local food-bank. I need to design a set of tables within the dev db which enables me to calculate and report on the number of items used in the boxes (and bags) collated by the foodbank for any delivery session.

Details are as follows:

• Grocery/Inventory items are classed as groups: ‘tin beans’, ‘pasta’, tin fruit’, etc. There are presently 25 similar categories and are listed in a ‘store items’ table. This is the lowest level of grain for the db, analysis and reporting.
• Grocery items are collated into a box. Each box holds 17 items.
• There are 5 different box types - each have different mixes selected from the 25 store items.
• For each session, X boxes may be collated, with each of A,B, C etc types being used.
• So, for each session a simple view is needed which lists the volumes of grocery items (store items) used in all the boxes collated.
• This view is then to be used to update a high level ‘current stock’ position...to which new donations are added (this part of the db and processing is already built and being tested).

Another way of looking at it may be as how to work out the shopping list of ingredients for a cafe with various dishes on the menu!

I’m building in Azure using SSMS and T-SQL. It is basic as is my current understanding of T-SQL! How can I simply solve this problem in terms of the db table structures, relationships and any particular code instructions that I may need to use in scripts or stored procedures? I've looked into arrays but not sure if this is the answer (ie box type against items).

Thanks!

question from:https://stackoverflow.com/questions/65939696/how-to-create-db-table-structure-to-capture-and-process-items-used-in-food-bank

Answer 1

Waitting for answers