Hello All Experts,


I have been after this for quiet some time now :-(. I want to design a
database for a Restaurant. As I am a newbie on the learning curve I need a
helping hand to guide me thru.



I have the following Tables.

RI = Referential Integrity, CU = Cascade Update, CD = Cascade Delete

1 to M = One-to-Many Relationship



Customer

-CustomerID (PK)

-other details as name, surname etc.

- StaffID (FK)

- OrderID (FK)



Table

- TableID (PK)

- TableDetails



Staff

-StaffID (PK)

-StaffName, DateofHire, etc

-StaffRoleID (FK)



StaffRole

-StaffRoleID (PK)

-StaffRoleDescription



Order

- OrderID (PK)

- OrderDate

- OrderTime

- CustomerID(FK)

- StaffRoleID(FK) (1 to M )

- MenuItemID (FK)

- TableID (FK)



OrderMenuItem

- OrderMenuItemID (PK)

- OrderMenuItemQuantity



MenuItem

- MenuItemID (PK)

- MenuItemDescription

- MenuItemPrice

- MenuItemQuantity

- CategoryID (FK) (1 to M with RI)

- OrderID (FK) (1 to M with RI, CU, CD)



Categories

- CategoryID (PK)

- CategoryDescription (eg Starters, Desserts, Cold Drinks etc. etc.)

- CustomerID (FK) (1 to M)



Payments

- PaymentID (PK)

- OrderID (FK) (1 to M with RI, CU and CD)

- PaymentAmount

- PaymentDate

- PaymentMethodID (FK) (1 to M)



PaymentMethod

- PaymentMethodID (PK)

- PaymentMethod (eg Credit Card, Cash, Check etc. )



OrderDetails

- OrderDetaisID (PK)

- OrderID (FK) (1 to M) RI, CU, CD

- MenuItemID (FK) (1 to M)

- Quantity

- UnitPrice

- Discount



This is how far I have got.

Can somebody guide me in restructuring my design. I must have missed
several basics. Pardon me for my ignorance.



Basically I want to design a Restaurant database where the Customer walks in
and places an Order from a Category (subdivided in MenuItems) which is
executed by a Staff. After the completion of the order the Bill will be
generated.



Any help or suggestions would be very much appreciated.



Rashid Khan