Restaurant Inventory Management

[Ashton]

I just started working on an FM database I'd like to use for managing the inventory of a restaurant. I want it to simultaneously keep track of inventory acquisitions and deductions (i.e. food orders) on an ongoing basis. Also it will track customer buying history, bids from distributors on various items, etc. Thus far I have these 12 tables and their associated TOs defined as follows:

- Classes (StockClasses, MeasurementClasses)

- Units (PurchaseUnits)

- Inventory (InventoryItems, InventoryStock)

- Recipes (Recipes)

- Ingredients (Ingredients)

- Orders|LineItems (Orders|LineItems)

- Customers (Customers)

- Distributors (Distributors)

- Bids (Bids)

- Invoices (Invoices)

- Contacts (CustomerContacts, DistributorContacts)

- Addresses (CustomerAddresses, DistributorAddresses)

Here is a screenshot of my current relations graph and all of the fields for each table:

I've never attempted anything like this, so I'm probably horribly off. My thinking is more or less along these lines: Inventory purchases and food sales are just two different kinds of transactions, so they should all be lumped under the same table (Orders). From there, conceive of every food/beverage item sale as effectively being an order for a certain recipe, each of which constitute a particular combination of inventory items in various quantities. So then tabulating existing inventory at any given time ought to be a simple case of "Current Inventory = Initial Inventory + Inventory Purchases - Recipe Orders" (sans real-world aberrations, of course).

Anyway. Everything seems to work so long as you're only on the right or left side of the Orders|LineItems table. My main issue at the moment is in getting both sides to properly coordinate with each other—i.e. the InventoryItems TO and all of its branches, obviously can't "see" from their context what activity is going on with recipe orders, etc. I can think of a few ways to bridge the two sides, but none of the approaches I've been able to come up with seem like very elegant solutions to the problem. Overall, what I'm looking for is tips/advice on how to simplify and concise this entire thing down, to cut down on some of the redundancy and attain a more harmonious arrangement between all components.

Edit: Apparently the forum or ImageShack made my screenshot tiny. Direct link here.

Edited May 14, 2010 by Guest

[Peter (duksis3)]

Hi,

I haven't go very deep in your solution but some years ago I make something similar and I use duplicated particular table(s) in the Graph (for another relationships) to get more easy working or (what you say) put left and right part together.

[dineshsmm]

Out Product Features:

• Inventory management
• Service time management
• Service type management
• Category management
• Menu management.
• Packages
• Equipment management
• Order Management
• Payment Management
• Quotation management
• Email component
• Employee management