Issue with restaurant database system

[ezybusy]

Hi Guys, i am trying to create a small database application for my small restaurant. And thanks to June7 i have been able to cover some parts of the application. However i am still facing a major issue.

The scenario:
The restaurant works on a Clients Table base. We don't take orders from outside. Clients come and eat in our premises, pay their bill and then leave.
So on the database application, i want to have a set of buttons representing the clients tables (we have about 12 clients tables) available in the restaurant. So that all records entered in the database system will be based on those clients tables.
Example: - Mister Johns comes and sits at Table 1, then orders some products. Before we records Johns order details in the system, we click on a button representing Table 1 and then we start recording all details.

The Problem:
I have been able to handle most part of the database system, except the Table numbering logic, and sales recording. In fact i don't know how to go about the following:

- Put a Client table on hold when that Table is occupied by a client or Add a new sale if that table isn't occupied:


Upon clicking on the button representing a specific client table, we see a list of all items ordered by the client occupying that table, else if the table is not occupied by any client then we add a new sale transaction and assign the Table number to that new sale.

- Have a button called "END SALE" to set a sale transaction as Completed when the client occupying a table leaves:
We will have a button that will set a sale transaction as completed based on a yes/no field in one of the database table (in the sales transaction database table i think). So that, when a sale transaction related to a specific client table is marked as "Completed", once we click on the button representing that client table, it opens in new record mode.

- Have a backup table where a copies of all sale transactions are recorded:
A kind of archiving database table where everything related to sale transaction is recorded, so that we don't loose anything like details related to sales.

i have attached a sample database Restarant system sample.zip that will give a more comprehensive idea of what i am trying to do. Kindly have a look at it if you think you can help me put the whole system together. Thank you in advance for the concern.

[ranman256]

The tTables table (tee hee) should have a field : ClientID
if tTables.ClientID = null then the table is available.
otherwise it is on hold and cannot be booked.

End Sale, fills in amt paid, and sets tTables.ClientID = null.

all transactions would also write to the tLog table. (backup)
Backup the database ,hourly, or daily.

[ssanfu]

I played around with your dB and your concept.

I had to unlink table "Sale" and "Sale_Details".
I renamed a few fields and added field "ItemPrice" in "Sale_Details". I presume that the price would change for items over time, so you have to save the current price in the record so the total can be computed later.

Added some code.......


You will have to create the reports (by date?) and the other things you (might) want.......


Good luck with your project...

Happy Halloween.

[ezybusy]

@ssanfu
Thank you very much.
i will take the time to understand all modifications and codes you included in the sample db then post back.

For the mean time i have a question: Having the product price column in two different tables doesn't it break normalization rule? Because it is like repeating the same information in a different place.

However i still like the idea, because for sure price will change over time.

[ArviLaanemets]

There are 2 possible approaches in such databases for saving price and calculating cost.

1. You have a table for current prices, and enter from there the price into order log (or whatever name you have for table you use to register client order rows). Order cost is calculated entirely based on current order info;

2. You have a table with prices history (for every article there is a list of prices with dates those prices are valid from. Order cost is calculated based on articles and quantities from order log and on price calculated from price history table for order date.

For your case the 1st approach is better choice, as you don't have to bother with locking prices. Let's imagine you use a 2nd approach with entered prices not locked, and you had a client. After the client paid the bill and left, you for some reason do change the current price for some article. As result you get different sums payd by client and calculated by program for this day.