restaurant menu

[Harrold]

Hi,

Please comment about my work.

I created two tables: 1) Products & 2) Menu Items.

for (1),
Product_ID (PK)
Product_Name (ie appertizers, salads,soups,set lunch, hot beverages, voucher, catering, etc)

for (2),
Menu_ID (PK)
Product_ID
Menu_Item
Price
Details (Description of the menu item)

Now i think i have an issue about this design.
1) For set lunch, there are choices made (even though the price still remain the same). Hence, how can i present it in the table?
2) Voucher, Catering are supposed to be in Product table?

Thanks

 

[GinaWhipp]

It would help to know the full scope of what you are trying to accomplish so we can better make suggestions.

 

[Harrold]

Thanks Gina.

I would like to build a system for a restaurant. for the time being, i am thinking about the daily sales report. and slowly expanding to inventory, debtor, booking, etc.

As such, only two tables are being created. I will develop more tables when i go along.

Thanks again

 

[GinaWhipp]

Building a database is much like building a house. You can't build the foundation for second floor and come back later and fix that to put in a first floor. My suggestion would be that you get those tables right now or you will be forever trying to *make your data work*.

And using the house analogy, you can pick out the furniture and you haven't even built the room. You are thinkiing about the Sales Report and the tables aren't set up yet.

My suggestion for tables...

tblProducts
pProductID (PK)
pProduct (Appertizers, Salads, Soups, Set lunch, Hot beverages, etc...)
pPrice

tblMenu
mMenuID (PK)
mMenuTypeID (FK)
mProductID (FK)
mDescription
mMenuItem - Not sure what goes in this field

tblMenuTypes
mtMenuTypeID (PK)
mtMenuType (Breakfast, Lunch, Dinner, Voucher, etc...)

tblReservations
rReservationID (PK)
rName
rReservationTypeID (FK)
rPartyCount (How many)

tblReservationTypes
rtReservationTypeID (PK)
rtReservationType (1, 2, etc..., Cater, etc...)

I will also suggest you read...

Jeff Conrad's resources page...
http://www.accessmvp.com/JConrad/accessjunkie/resources.html
The Access Web resources page...
http://www.mvps.org/access/resources/index.html
A free tutorial written by Crystal (MS Access MVP)...
http://allenbrowne.com/casu-22.html
MVP Allen Browne's tutorials...
http://allenbrowne.com/links.html#Tutorials
Sample data models...
http://www.databasedev.co.uk/table-of-contents.html

And to help with Naming Conventions...
http://www.granite.ab.ca/access/tablefieldnaming.htm
http://www.regina-whipp.com/index_files/NamingConventions.htm

 

[Harrold]

Thanks again, Gina.

I will refer to your table structures while building it.

I just include:

tblTables
tables_ID (PK)
Outlet_Location (FK)
Tables_No (eg, Table 1, table 2, table 3,etc)
No_of_People (eg Table 1 can sit 2 persons, etc)

TblOutlet
Outlet_ID (PK)
Outlet_Location

My issues are:
1) When i tried to relate Outlet_location, the relationship shows is indetermine. I am a bit confused now. Shall the relationship be one to many?

2) My relationship windows have two tbltables? How can i delete another one?

thanks

 

[GinaWhipp]

Before I comment, what is an Oulet? Is that other locations of the Restaurant? I want to be sure I answer it correctly...

 

[Harrold]

Yes Gina. It is another branch/location of the restaurant.

 

[GinaWhipp]

Table adjustment...

tblProducts
pProductID (PK)
pProduct (Appertizers, Salads, Soups, Set lunch, Hot beverages, etc...)
pPrice

tblMenu
mMenuID (PK)
mMenuTypeID (FK)
mProductID (FK)
mDescription
mMenuItem - Not sure what goes in this field

tblMenuTypes
mtMenuTypeID (PK)
mtMenuType (Breakfast, Lunch, Dinner, Voucher, etc...)

tblReservations
rReservationID (PK)
rName
rReservationTypeID (FK)
rPartyCount (How many)
rRestaurantLocationID (FK)

tblReservationTypes
rtReservationTypeID (PK)

tblTables
tTableID (PK)
tRestaurantLocationID (FK)
tTableNumber (eg, Table 1, table 2, table 3,etc)
tChairCount (eg Table 1 can sit 2 persons, etc)

tblRestaurantLocation
rlRestaurantLocationID (PK)
rlLocation

Instead of tblOutlet I choose the name tblRestaurantLocation as it is clearer. You want names that *anyone* can identify as in.. you post a question about queries or whatever and the Answerer doesn't have to guess what you are storing in that table. And let's try and eliminate those underscores, they are a pain in the neck to type, especially when coding.