question about a Reservation database (help)

[rogueblade]

Hey guys. I'm used to building databases that don't change too frequently and have structures that are pretty common. For eg a music database; which would have multiple tables for categories like artists, tracks, albums etc. and your php would bring the necessary information together.

But I want to build a restaurant reservation system for a school project and while scoping out the idea and functionality and what not I got to the stage where I need to sketch out my database structure.

I realize this will be quite different in the sense that the database will be very active and dynamic, changing constantly on the fly with reservations being made, changed, and tables being added, moved, changed etc.

So I'm wondering what sort of structure will my database need? Would I break it down like any other database and have a customer table, a tables table, a reservation table etc. Or would you just create a reservation table that would have all the necessary fields such as date, name, time, table, party size, notes etc

thanks!

[ignace]

You won't need a customers table because have you ever had to give your full details when you went to a restaurant? No. all required information comes down to a reservation name (which can be anything), date and time of the reservation, and the number of people accompanying you.

 

Possible use-cases:

 

1. A customer cancels his reservation

2. A customer has a coupon code or some other means (birthday, ..) by which he receives a reduction on his meal

3. The owner wants to find the most popular dish

4. The owner wants to find out if all none cancelled reservations have been paid for this day, this month, ..

5. The owner wants to find the total income (excl VAT) for today, this month, year, ..

6. The owner wants to find out which is the most popular choice of payment among his customers

7. ..

 

menu (id, name, description, price)
consumption (menu_id, reservation_id)
reservation (id, tabletop_id, for, at, size, cancelled)
tabletop (id, name, seats)
checkout (id, reservation_id, subtotal, total, paid_by, coupon_code, reduction)

 

This is by no means the end try to find some more use-cases discuss with your teacher, your parents or visit a restaurant. You don't need all information but you should have a good idea of the basic workflow within a restaurant.