Hi SF,
I get the impression that you and your friend need to spend some more
time thinking about how his restaurant works and what problems he
expects the database to solve.
More comments inline.
My friend ask me to help him developing a database for his restaurant and I
come up with 3 tables.
tblMenu
MenuID
menu_Description
What about specials, set meals, customers who want boiled potatoes
instead of sauté with their steak, and all the other little subtleties
of real life?
tblTables (tables in the restaurant)
TableID
TableLocation
What about large parties for which he puts two or more tables together?
tblTransaction
TransactionID
MenuID
TableID
Does this mean there's an separate transaction for every item ordered by
every person in a group? It seems more likely that a Transaction would
consist of one group of people sitting together:
tblTransactions
TransactionID
TransactionDate
TableID
NumberOfPersons
TimePaid (date/time)
tblTransactionItems
TransactionItemID
TransactionID (FK into tblTransactions)
Entered (date/time)
MenuID (FK)
SpecialHandling (text)
My question is
1 - how do I know that a specific table has paid the bill (since the same
table will be used subsequently by different guest)
If the TimePaid field of the relevant transaction is Null then the bill
hasn't been paid.
2 - Did my table structure above is good enough to handle this situation.
Probably not - but I'm sure the two of you haven't thought hard enough
about all the situations you want the database to handle. For instance,
you mention the bill, but nothing about prices and adding it up. How
about gratuities (esp. with credit card payments? Parties who want
individual bills but who need to be served at the same time? Account
customers? and so on and so on. Dishes that get sent back? Corked wine?
A pen, paper and Post-It system gives the flexibility to handle these
and other exceptions, but with a relational database you have to work
them out in advance and understand how they will be handled.
