150 Relations (To slow)

[SharkFin85]

I've made a booking system for a guest house. As you can see at the pictures, there are 6 rooms (left) and 25 days (Top, 5 past days + 1chosen day + 19 next days), you choose the date from the small calendar on the top left and it appears on the 6th column(Green). In the portal just under the Booking Grid, you can see all the reservations of the date that is in the green rectangle, and if you click on the blue round on the left of each record you it send you to the Booking Details Layout so you can change some details about the selected reservation.

My Big Problem is that I have 150 different relations just for the Booking Grid (above the portal) and it make it very slow. The relation are connecting (for example): The 3rd date with the arrival and departure date (only if the 3rd day is >=arrival date and =

I hope my description was easy to understand....

If someone have an idea for how to get off of thes 150 relation please help me... :

THANKS

123.zip

456.zip

Edited May 11, 2007 by Guest

[SharkFin85]

SomeOne??

[Fenton]

OK, I'll take a stab. I can't really tell what the records are in the layout that we're looking at. It seems to me that you've defined separate fields for everything in the same table. Rooms: Nile, Congo, etc.. Days: Day1, Day2, etc.. But I can't tell what the records in that table actually are, if anything. It looks like a 1-record table.

While this works, and gives you a lot of flexibility, you pay a price in speed. In your structure you need a set of relationships for each combination.

The Rooms are on the left, hence could be records (rows), hence could be only 1 field, Room, in a Rooms table, only 6 records, with values: Nile, Congo, etc., in different records.

The Dates are at top, in the columns position, hence would be, as they are now, fields. But they could be unstored calculations based on one global date "anchor" (gDate +1, gDate+2, etc.). Changing the global changes the range.

So, the relationship set would be: Room AND gDay_n to Room AND Day in the BookingDetails table.

Then you'd only need that one relationship set, 25 relationships.

The multiple entries for a Room-Day would be shown in a little portal in the body; one portal per relationship, of the 6 records viewed as list. This is normal for calendar/scheduling solutions.

The above would present what you've got in your top portal (I assume it's a portal). What I'm proposing is: List view of 6 records, 25 portals in the Body part; not 1 portal.

The bottom portal could be the footer part, based only on the currently choosen gDate, 1 relationship. The calendar would be in the header part.

I would think that the "Add" button should be down by the lower portal, so it could add an entry for the chosen date. Saves having to enter the date. Also forces people to look the room availability before booking.

[Søren Dyhr]

Alright I do not with my english skills understand what Fenton here writes, therefore have set out to give my version of the problem since I feel we all have been there, with an exaggerated utilization of relations not really catered for, I feel the most of functionality can be optained with the above graph, which uses:

http://www.fmforums.com/forum/showtopic.php?tid/176396/post/204083/hl//

...which in reality is the same as this:

http://www.fmcollective.com/2007/04/just_add_the_ta.html

Now, the attached template to this thread, have been facilitated with a button in each cell of the grid, making it posible to either make a new booking (lower dongle in the graph) or edit an existing booking (going to the right in the graph)

Anyways, examine the principle - there could have been a bug or two I havn't noticed, but as such should you get the gist of it - otherwise don't hessitate to ask further.

--sd

grid.zip

[comment]

Check your calcs in the Utilizer table - there should be 5 * 17 = 85 records, and the dates should repeat cyclically.

Adding a table for Rooms would be also appropriate, IMHO.

Edited May 14, 2007 by Guest

[Søren Dyhr]

Check your calcs in the Utilizer table - there should be 5 * 17 = 85 records

Ah! yes the original was 19, but it couldn't fit my screen in 100%, So I just cut them off - so yes perhaps a bug... but isn't it just a tinier view??

The issue with the ability to add more rooms ...is it what Fenton is on about with portals??

--sd

[comment]

Well, I don't know what the significance of 19 or 17 is. I would have gone for 14 or 21 (multiples of a full week). In any case, the magic number to use here is 5 - the number of rooms. This way you can also cut the number of portals by a factor of 5 - one portal per column.