Help - Having Issues With Database

[hurly1223]

---

I'm creating a database for a restaurant using Microsoft access - they will use it on their tablet computers with each patron at the restaurant being given a tablet as they sit down and they can then order their food and drink from the tablet with the order


directly transmitted to the chef an d kitchen staff to prepare. Waiters will just be needed to welcome patrons and serve the food once it is prepared.

Successful scenario
• The Diner is seated
• The Waiter takes Tablet to Table
• The Customer enters order into the system/tablet
• The system alerts the Kitchen staff of the order
• The Kitchen staff prepare food
• The system alerts the Bar staff of the order
• The Bar staff prepare drinks
• The system alerts the Waiter that the drinks are ready
• The Waiter delivers drinks to the table
• The system alerts the waiter that the dish is ready
• The Waiter delivers the dish to the table
• The Diner orders a Desert
• The Customer enters the order into the system/tablet
• The system alerts the Kitchen staff of the order
• The Kitchen staff prepare the food
• The system alerts the waiter that the dish is ready
• The Waiter delivers the dish to the table
• The Management decide to issue a discount from the bill
• The Waiter uses the system/tablet to print out the bill
• The Diner offers payment
• The Waiter enters the payment details into the system/tablet
• The Management uses the system to view the statistics

Trouble:

• I'm Having Trouble With The Relationships Between Tables / Also Not Sure Exactly If I Have The Right Fields In The Table
• Having Trouble Making A Macro - Main Menu Has A Button For The Menus - Type In For Menu Type "Dinner, Lunch Etc." When I Click Button It Runs Query Parameter - Type In "Dinner" Then It Goes To "Dinner Form Menu" If I Type "Lunch" It Should Go To "Lunch Form Menu"
• I Have No Knowledge On How To Have A Button Next To An Item On The Menu That Is Add To Order - Then Finalize Order - So Then It Can Be Displayed On The Kitchen Interface With All The Orders To Be Cooked

I'm Struggling With This Because I Only Have Basic /Above Average Knowledge Of Microsoft Access

So If Anyone Could Help Me Out I Would Be Very Grateful - I have Done 80% Just These Things Are Stopping Me From Finishing

Picture Of The Relationships
http://i.imgur.com/LLZpWRA.png

[Dal Jeanis]

If you're struggling with the table relationships, then you probably aren't 80% done. Design of the underlying database is in the first half of the job.

Some of your questions are about table design, some are about interface design, some about transaction. All depend on you having first defined the right business process. When designing a business process, you need to include the common exceptions and unsuccessful scenarios in your design as well. I've mucked around with your "successful scenario", and broken it up a little more into specific sub-processes. Please review my quick breakout (below) and tell me where your process differs from this. Then we can talk about the table relationships.

If the tablets aren't already Permanently physically tethered to the tables, then you'll need to ensure the tablet knows what table and what waiter it's working for. You probably also have to deal with the "separate checks at one table" scenario, which I have not dealt with here, except for adding it as a line-item near "Waiter prints the bill".

GREETING AND SEATING
• The Waiter seats the customer.
• The Waiter takes Tablet to Table.
• The Waiter inputs the table number and Waiter ID into the Table Tablet, and sets the tablet to expect a new customer.
• The Waiter explains Tablet usage to Customer, and walks the Customer through entering their name and their drink order.

ENTERING ORDER (CUSTOMER)
• The Customer enters order into the tablet.
(the system verifies that each item ordered is available)
(The same customer can enter multiple orders in a session, sending them to the bar, kitchen or waiter when the customer has verified the entry. For example, the customer can enter a drink order, finalize it, then order an appetizer, finalize it, order the main meal, finalize it, then order a dessert and finalize it)
• The Customer can enter an alert to ask for a Waiter or Manager's help.
• The Customer can enter an alert to ask for drink refills.

ALTERATION TO ORDER (CUSTOMER)
(If a customer needs to update an order that is in process, then what (?))

FULFILLMENT OF FOOD ORDER
• The system alerts the Kitchen staff of the finalized order
• The Kitchen staff prepares food
• The Kitchen staff tells the system that the food is ready
• The system alerts the waiter that the dish is ready
• The Waiter delivers the dish to the table
• The Waiter informs the Waiter's tablet that the dish has been delivered (I'd make him click the dishes off individually)
• The tablet informs the system that the dish has been delivered

FULFILLMENT OF DRINK ORDER
• The system alerts the Bar staff of the order
• The Bar staff prepare drinks
• The Bar staff tells the system that the drinks are ready
• The system alerts the Waiter that the drinks are ready
• The Waiter delivers drinks to the table
• The Waiter informs the Waiter's tablet that the dish has been delivered (I'd make him click the drinks off individually)
• The tablet alerts the system that the dish has been delivered

ALTERATION TO ORDER (STAFF - ITEM UNAVAILABLE)
(If an item is not available, or special instructions are not understood, then the Kitchen staff/Bar Staff does what(?) and the system alerts the Waiter/manager to contact the customer and do what(?))
• The Bar Staff/Kitchen Staff/Waiter/Management updates the order as necessary
• The Bar Staff/Kitchen Staff/Waiter/Management communicates issues to the customer HOW (?)
• The Management decide to issue a discount from the bill

ALTERATION TO ORDER (STAFF - FOOD MADE WRONG)
(If an item is made incorrectly, and needs to go back to the kitchen or Bar, then (what!)
• The Waiter updates the order from the Waiter's Tablet to indicate a RUSH reorder of the error item.
• The System alerts the Manager of the error item, so Manager can visit the table.

BILLING AND PAYMENT
• The Waiter uses the system/tablet to print out the bill
(collecting up all the various orders and delivery information, the system will query the waiter about delivery of any items that are not already marked as having been delivered.)
• If separate checks are requested, the Waiter uses the system/tablet to separate the charges into different checks as requested.
• The Customer(s) offers payment
• The Waiter enters the payment details into the system/tablet

WAITER OVERSIGHT
• The Waiter can see details of the waiter's tables, food status and drink status
• The Waiter can review orders as they are input and verify they make sense
• The Waiter can review status of Food or Drinks ordered but not yet delivered
• The Waiter can review Alerts received from Bar, Kitchen, or Table
• A Waiter can temporarily receive another waiter's alerts (enabling waiters can go on the legally mandatory breaks)

MANAGEMENT CONTROL
• The Management can transfer an open table/customer from one Waiter to another, either permanently or temporarily (to allow waiters to go on break, swap tables, go off shift early, or whatever)

MANAGEMENT OVERSIGHT AND REPORTING
• The Management uses the system to view the statistics
• The Management can see details of any waiter's tables, food status and drink status
• The Management can review orders as they are input and verify they make sense
• The Management can review status of Food or Drinks ordered but not yet delivered (by Waiter, by table, by section)
• The Management can review Alerts received from System, Bar, Kitchen, or Table (for any Waiter or Service area)

CLEARING TABLE
• The Waiter tells the system that the table is ready to be cleared
• The System alerts the Busboy to clear the table
• The Busboy clears the table
• The Busboy tells the system that the table is cleared
• The System alerts the Waiter the table is available

A couple of notes/questions.

1) A dessert order is no different in processing than any other food order, so no special handling is required there. You may have desserts on a different menu, but the background processing is the same, and you will have several different menus anyway. (breakfast, brunch, lunch, dinner, appetizers, dessert, and so on)

2) Is a nonalcoholic drink order handled by the waiter directly, or does it go through the bar? The above assumes that ALL drinks go thorugh the bar. Otherwise, a drink order should look like this -

FULFILLMENT OF DRINK ORDER
• The system alerts the Bar staff of the alcoholic portion of the order and alerts the Waiter of the soft drink portion of the order
• The Waiter delivers the soft drinks to the table
• The Waiter informs the Waiter's tablet that the soft drinks have been delivered (I'd make him click the drinks off individually)
• The Bar staff prepare drinks
• The Bar staff tells the system that the drinks are ready
• The system alerts the Waiter that the drinks are ready
• The Waiter delivers drinks to the table
• The Waiter informs the Waiter's tablet that the alcoholic drinks has been delivered (I'd make him click the drinks off individually)
• The tablet alerts the system that the dish has been delivered

Please look that over, and adjust anything that doesn't fit your restaurant. If you don't really know how your particular restaurant works in practice, then please run it by either the manager, hostess or maitre'd, and then run it by at least one server-level person. Better to iron out the process requirements before you try to code it, or no one will want to use the system.

Then we can adjust the table design.

[ssanfu]

Great concept!! Amazing that such a simple idea gets very complex so fast.

Impressive analysis by Dal... I don't know much about the restaurant business, but I understand a lot more now.

I am curious on a few things: does the tablet replace paper menus or is it an ordering aide? One tablet would work for a single person or a couple, but what about a family of 5?

What about large groups? Say a group of 15? There's that separate checks thing again...

....and then run it by at least one server-level person.....

Finally, someone that understands the person(s) that end up using the system needs to have major input in the design.

[Dal Jeanis]

ssanfu - thanks for pointing out those two scenarios, which would also need to be addressed. This app may be a concept that would only be feasible in a "bistro" environment or bar with tiny tables and a manageably small menu.

I've never actually run a restaurant, so my walkthru only brushes the surface, based on the observational skills of a skilled business analyst, walking in memory through 20-30 episodes of Restaurant Impossible. Even my expanded version of the walkthru is a very limited view of the food service process.

I altered the "Greet and Seat" scenario because it was obvious to me that (1) most customers will need to be introduced to the app and interface, (2) the App MUST know which waiter and table it represents, (3) customers are accustomed to giving their drink orders first, and (4) it gives the waiters a first (and maybe only) chance to upsell, perhaps with appetizers or perhaps with the daily special.

The management who are attempting this concept don't envision the waiters as profit centers, so it is clearly not being written for an upscale establishment that trains their waitstaff in product promotion or upsell. I'd expect that implementing this idea will actually cost 10-20% off the profit margin, and management will never know why. (Unless they implement auto-upsell at the tablet level - not likely given the current design parameters.)

After just doing brief review and analysis, I'm more worried about enabling the customer to easily and clearly enter "I'm deathly allergic to dairy and any accidental contact with cheese particles will kill me". The consequences of poor interface design in this kind of situation would be only a minor drain for the company, and cause the concept to fail, but it could also result in serious harm to vulnerable customers.

I also haven't dealt with guy-goes-to-bathroom-and-some-joker-orders-him-food, or with the advantages and disadvantages of my-iphone-can-pretend-it's-a-tablet. This kind of a restaurant really SHOULD have an app so that you can set up your order in advance and transfer it when you are seated.

However, this is an Access forum, not a business analysis, promotion, marketing, etc forum, so it's beyond the scope of the thread...