User Requirements
These Requirements were established by Sean on 27th. June, 2007 who
needs a Database to support his mother's Catering Business.

My mother runs a catering business right now, and she is the contracted caterer for the French School of Nairobi, Kenya in Africa.
The school has 400 students, while there are 30 staff.

From Monday to Thursday, she provides lunch for almost 300 people, consisting of some teachers but mostly children
ranging from kindergarten level to high school level. At the beginning of each term,

Parents can opt to make payments for their children's lunches for the whole term, and most do, but a few
decide to decline school lunch and leave their children to fend for themselves when it comes to getting lunch.

Every day at lunchtime, a supervisor carries a list with all children's names who have had school lunch paid for
the term, and checks their name off the list if that child has come to take lunch.

There are instances, however, when a student likes what my mom is offering but has not paid for school lunch and he
would like to have some,

He goes to the supervisor who notes down his name and he gets a meal voucher, good for one meal.
That lunch will be accounted for and added to the student's fees to be paid at the end of the term.

There are various student categories in which the students fall, and each has different price brackets:
$2 for the kindergarten level,
$4 for the primary school, and $6 for high school students and teachers.

Those prices are per day per person depending on their level of education.

Those prices are also flat rate for any student, even those who have not pre-paid for lunch.

If a primary school student wanted lunch and he had not pre-paid, his meal would be worth $4.

I think it is worth mentioning here that a meal does not consist of a single serving.

A student can have more than one serving in a meal if they feel so inclined, but most find one serving satisfactory.

The kitchen side of the catering business is a little more straight-forward. My mom has a list of different
ingredients she requires to produce the food that she needs to everyday, and these ingredients are stocked in the store room.

These ingredients come from various suppliers, but my mom has made an arrangement so that there is only one
supplier for certain goods.

For example, meat comes from only one supplier, chicken comes from only one supplier, the fruit and vegetables
comes from only the greengrocer, the dry goods come from a wholesaler, etc...

At the beginning of each term, my mom's stock starts from zero, so she buys enough stock for the first month
including extra quantities of pantry goods, such as salt and spices, that are going to be used frequently.

At the end of every month, or whenever necessary, she will reorder to refill stock that has reached the reorder
level for that item. When the items are delivered, most of the time the deliveries contain the complete order,
but sometimes the delivery will only contain half or 2/3 of the items ordered, so my mom will have to wait until
the supplier completes the order.


The problems with the way things are running now are on both ends:
* On the actual serving side, it is possible that some students will end up having a meal without having gone to
get a meal voucher, and they say that they are on the list, and since my mom's staff do not know all the students
and thus cannot tell which ones have paid and which ones have not, we make losses in meals that go unpaid for.
  There are also issues with inconsistency between our count of how many meals were consumed and the supervisor's
count, which is important because my mom wants to be paid for the meals that were had, not just the ones that were
noticed to have been had.
The losses, while most of the time small, are losses none the less and money that my mom could be earning.

* On the stock side, due to the manual nature of the inventory keeping at present, it is easy to lose track of
items that we have in stock and more often than not excess amounts of items are ordered when they're not necessary
because there are already enough in stock.
  It is also difficult to account for inventory used when taken to produce the food, so the kitchen store is usually
left in need of restocking when the store's levels are low.
  There is also the matter of record-keeping of all orders and invoices, as well as delivery notes.
Currently, it is easy to lose track of the quantities pending of items when suppliers deliver only part of the order,
so the potential to lose money reordering or to believe that there is enough in stock when there is not is quite high.


As such, I plan on computerizing my mother's catering accounting through the use of an Access database. Using this
computerized system should be able to tackle the problems on both sides by:
* taking account of every single meal sold, and depending on level of education, determining how much each meal is
worth so as to gather the figures and make a total for gross pay owed to my mom.
* take account of inventory levels, take account of quantities used and keep record of orders and deliveries, and
when the deliveries are made, to update the stock levels according to what has been received. These records will
help figure out the total my mom owes suppliers.

With the assistance of this program, my mom will be able to accurately tell, by deducting the money owed from the
gross owed, the net amount that she is owed at the end of the term.

I decided to do this in Access because it seems the simplest method of creating a good database, but I did not know
Access well and in fact had to get a book to teach me how to use it. The book has taught me a lot, I understand how
tables and relationships work, and I have been able to draw out these tables so far, but I'm having trouble linking them.
Please refer to the diagram I have attached to see what the state of my database is like so far.


As you can see from the diagram, I have two sections: meals and stock. On the stock side, I have a Suppliers table,
a Products table, Order Details and Order tables, and a Deliveries table.

I believe the way I have organized the tables and the joining relationships makes sense, but I don't know how to
store the data such that I can record deliveries according to which order they belong to (since each order is unique
and each order comes from a different supplier) and then update the stock levels of the inventory by the amount received.

I also have a feeling that I'm missing a "Quantity Of Inventory Consumed" table somewhere, but I don't know where and how.

On the meal side, it is a lot of the same thing. I have a Students table, which will keep all students in the school in
record assigned by their unique ID numbers, as well as whether they have paid for lunch beforehand or not.

Then I have them linked to the Meal Plan table, which describes what each plan costs ($2, $4 and $6 respectively),
therefore being able to identify which plan goes with which student. My problem on this side is knowing how to create
another table, or re-organizing the database and its elements such that I can make a record every time that a student
(pre-paid or not) will be having lunch.

================================================

The Things of Interest include :-
Ingredients
Meals
Recipes
Staff
Students