Restaurant Reservations Entity-Diagram Relationships

[Marco D]

Basically, what I would like, is to get help or guidance (or a template to start from, if any exist), with respect to building my Data Structure and come up with the appropriate 'strategy' to build my Entity-Relationship Diagram for a small restaurant reservations system.

Ultimately, I want to have a 'Daily Floor view' (see pic.1), where I would see all my tables for any single day (back and next arrows to flip through days), the tables being represented by small portals that simply show the time and last name of any given reservation. Clicking a name would lead to the reservation's details (pic.2).

I'm following the VTC FileMaker modules right now, and I would like to use my reservations system as a 'term project', but I have a hard time figuring out the proper ERD (what should be a FileMaker table, vs. what should be a record, etc.) I want to build a small, simple, proof of concept that emulates our current VB-based guest book (which holds no history!).

Also, I was thinking of inspiring myself from John-Mark Osborne's 'Calendar' (although it's WAY over my skill-level at this point) in order to have a "Monthly view" (button would create a new reservation, and a calculation would indicate the number of reservations so far for each day of the month), weekly view, with a list of reservations in each day (click on the name to access the full reservation details), etc.

The daily view would be very different, as I would like it to show my restaurant tables as small portals (4 columns of 10 tables) that would just show the time and last name of a given reservation (ex. 17:00 Smith). Again, clicking on 'Smith' would lead to the full reservation details.

Right now, I tried setting myself up this way:

Tables:

DAYS (with a single field to have 1 record per day, and use it to show my daily floorplans)

TABLES (have a date field, related to my DAYS table, an ID for the table number, and a kf_reserv field to relate them to my RESERVATIONS table)

RESERVATIONS (have a unique ID, a time, a client name (for now, which I would later want to relate to a CLIENTS table through a client_ID...) and a kf_table to relate to the right table)

I visibly do not have the appropriate data structure since I can't get my information to flow properly in daily or tables view.

If anyone can point me to a good template, or help me with designing my 'ERD logic' and data structure, I would be very grateful.

p.s.: I know I am tackling something for intermediate/advanced users, but I'm learning and would love to learn while doing my real project.

Thanks in advance,

Marco

[Fenton]

My philosophy of design is to ascertain what the objects are, in real life, then build the structure to contain them, in the hierarchy that is most logical. I expect that's everyone's philosophy -]. But it is important to remember I think, especially when beginning, when you sometimes get overwhelmed by the overall plan, while being bogged down by technical details.

So what you've got are Reservations and Tables. A Reservation contains:

Table identifier

Time (and date), as a 15 min time "slot" in this case

Name of person making reservation

Number of people

A Table probably only has a number. But it will be used to identify the different portals, relationships. So I'd create a real table for them.

So you can see that the data itself is not complex. What is more complex is how to view the data. I can think of two ways; a "time grid" view, and a "floor layout" view. Each of these would be useful, but for quite different purposes.

The time grid view would be used by the person taking reservations, showing the time slots for tables, for the day (which can be quickly changed to view a different day). The table identifier would show along the top, as labels. The actual records would be the 15 minute time slots. The data would be the name of the reserver and the number of people.

The limitation here would be the width of people's names; so you could only see so many tables at once (scrolling sideways to see more). If you created an alternate view, only showing the number (or just a color), then you could have very narrow columns and show many tables at once.

The other view would be the "floor layout" view. It would show the tables as they are on the restaurant floor. I don't know all its uses, but I've seen ones for waitresses, to show what goes where. It would be limited to only show a few times, a narrow range of times; like your FloorPlan.jpg. The relationship could be based on the current time, a little each side of it, and refreshed often; possibly using a global Timestamp, so that you could jump forward if you wanted to.

A lot of this depends on the size of the screen you've got available; the bigger the better.

This is a screenshot of my "time-grid" view, with the 15 slots as the records, the people as labels at the top. The blue arrow at the top is for "scrolling" to see more, earlier people (alphabetical).

[Fenton]

Actually, upon further thought, I don't know if you'd even need the "time-grid" view. Because I expect people calling for a reservation would have a fairly fixed idea of when they wanted to come. The time grid would be useful if you were so busy that people called in and asked "When's the soonest you can give me a reservation?"

[Marco D]

Thank you for your help Fenton,

I have successfully built a small FM database to replicate our reservations system. I used John Mark Osborne's Calendar tutorial to build a table of 'DAYS' in order to have a record for every date. With his script, I was able to 'create' 2007 and 2008...

I've create my tables like this:

DAYS

TABLES

RESERVATIONS

CLIENTS

By setting up a portal in a new DAYS_RESERV layout, I am able to see a list of my reservations with: Time, number of guest, table, and status (Reserved, Arrived, Completed, Cancelled, No Show).

Building on top of JMO's Calendar tutorial, I was also able to create a monthly view, and display a calculation of the number of reservations for any given day (very useful for us).

My problem is to create a layout where I can represent our floor plan with 1 portal for each table, in order to be able to show each table's reservations (with scroll-bar if needed). Is that possible ? I tried using a portal filtering technique, using an xTable global field. It works to list the reservations of a specific table, but I can't create 30 portals to represent all the restaurant's tables. How can I do it ? Is my best shot to use a lookup ? A report ? All I want is to be able to show:

Table 1

17h00 #Guests LastName

20h00 #Guests LastName

Table 2

17h00 #Guests LastName

19h15 #Guests LastName

Table 3

... 4 columns of 7-8 tables like this.

Clicking a reservation line would open the Reservations layout in a popup window.

What technique and/or ER should I use to achieve this ?

Thanks in advance,

Marco

[Marco D]

Attached is my barebones restaurant reservations file. I am pleased with the basic behavior so far even though its pretty rudimentary. Where I'm blocked, as you will see when you open the file, is when I try to design my: DAYS_RESERV_FLOORPLAN layout. I created a 'mock-up' just to illustrate what I'm trying to accomplish here. Right now, it's the same portal duplicated over and over, and its showing the reservations of the DAY for ALL tables. I would like each small portal, or other representation mechanism, to show a list of reservations for each specific table. DAYS_RESERV_TABLES-FILTER allows to choose a specific table (using a global field) and filter reservations to a single table. But I am unable to replicate this and show all the restaurant's tables at once on the FLOORPLAN -(

The behaviors I am trying to create are:

1-Represent each table with a mini scrollable portal (or ANY other way if portals are not the way to go here)

2-Be able to click on a table's button (left of the portals) in order to open a new reservation window.

3-Be able to click on a specific name to open the client's or reservations details window.

4-Change a line's color to red when the status is changed to Arrived. Do we need FMP9 to achieve such formating, or can it be done in 8.5 ?

I am not sure what table occurences and relationships I need to create to establish what I am trying to do. Any help with solving my roadblock will be much appreciated.

Thanks in advance,

Marco

Reservations_FMF.fp7.zip

[Fenton]

This is sort of what I had in mind. I created my c1, c2,... calcs in Days, so I could see whatever reservations for that day as a floor plan. I changed your relationship graph to match.

I'm not sure how you're going to add Clients while taking a reservation. You shouldn't put invisible buttons in the 2nd row of portals. It belongs in the 1st row if anywhere; if the field is not enterable, then just attach it to the field.

Reservations_FMF_fej.fp7.zip

[Fenton]

One change that might confuse you, was that I changed the table occurrence (TO) of the two layouts I'd modified to Days, instead of Days Reserved. Days is the "anchor" table occurrence of the Days table. Days Reserve is just a table occurrence of the Reservations table occurrence group (TOG, a group of TOs tied together).

Generally main layouts should be based the anchor of their base table, in their table's TOG, not on a little TO hanging off some other table's TOG.

Another reason Days should be its own TOG is that there will be many (30+) of those c1, c2,... relationships in it. You wouldn't want all those in some other TOG; best to keep them off the side, more or less.

[Søren Dyhr]

Couldn't we get these two threads merged Lee???

http://www.fmforums.com/forum/showtopic.php?tid/191738/post/274234/hl//fromsearch/1/#274234

...and this one!

--sd

[comment]

I wonder if this can't be simpler.

TableReservations.fp7.zip

[Søren Dyhr]

Well I have thought as well, I find the original approach with a portal for each table wrong, how would the booker handle a request for a seating near the windows, or as far from the performing band as posible, it would be task to scroll thru each portal in that direction to eyeball a timeslot.

I've desided to give it a stab as well! As you will notice have I refused to deal with relation number 1 to relation number N in a single layout, what if the business becomes a success, each portals rendering is going to burden the solution.

--sd

restaurantSD.zip

[Fenton]

Yes, Comments is simpler (and faster). It exploits the fact that you are viewing "rows" of tables, in that it show all the columns of one row in a single record. Mine, which has n columns more relationships (4x in this case), would be better if the tables don't form that kind of pattern, if you want to arrange them on the layout however you want.