Redesigning a relationship and calculations need help with a very tricky problem

[iankh]

I posted this on the Portal Relationship forum but perhaps it is really a calculations issue?

 

I currently have an application that I've distributed and am finding that one of the tables is growing wildly, difficult to maintain and am thinking about redesign and was hoping someone might be able to give me some suggestions.

 

I started this system when I virtually knew nearly nothing about FM and so, 2 years later, this redesign will also hopefully correct the sins of the past.

 

Some Background

 

The system is a vacation planning and management system, specifically for people who own a type of timeshare.

 

The ownership works by owning a number of "points." Each resort has point charts, to let the owner know by room type, date and day of week, how many many points a room will "cost."

 

The points are determined by "season." There are 5 "seasons."

 

Therefore a studio in a specific set of days in September may be 8 points Sunday through Thursday, and 14 points Friday and Saturday.

 

The same room in December may be 22 points Sunday through Thursday, and 32 points Friday and Saturday.

 

The start and end date for each "season" are updated every year.

 

The seasons appear many times during the year, to slice the year down, so "premier season" can happen during choice weeks in the summer, spring, Christmas, etc.

 

Current Relationships and Table Structure and the Problem

 

I have points table that essentially has a record for every resort, for every room type for every day of the year. There are currently over 96.000 records in the table!

 

It is difficult to maintain and also make updates when the dates for a new year are published, as that means creating thousands of new records.

 

The resort booking is related by resort name to the points table. There is a TotalPointsForBooking field that is a simple calculation, summing up the points from the related records.

 

Dealing with Friday and Saturday being higher or a reservation that crosses over seasons is simple, because each record in the points table has the points for that night.

 

The relationship and the calculation are working. It's just the points table is turning into a monster.

 

I am really sort of at a loss as to how to approach this differently.

 

Not Sure How to Change it, and the Calculation

 

I was thinking of redesigning the points table to into several tables with a nested relationship:

 

Year

- Resort

- Season with date ranges

- Room type with points for Sunday through Thursday and Friday and Saturday

 

This would drastically reduce the number of records but also really increase the complexity of the relationship of data.

 

I'm am lost as to whether this is a good approach or not.

 

The big problem is within a date range, figuring out weekdays and weekends.

 

First I'm not sure how I take a CheckInDate and CheckOutDate and determine which nights in the range fall into Sunday through Thursday and which into Friday and Saturday.

 

Then I need to do the ultimate reservation TotalPointsForBooking field calculation has me stumped.

 

Then there is the lookup which could cross years and seasons and finally the ultimate TotalPointsForBooking.

 

Any suggestions would be sincerely appreciated.

[Weetbicks]

Hi Iankh

 

Here is what I'm thinking, keep in mind I may be off track here because my reasoning above might be wrong:

 

 

 

Your basic resort/room structure:

 

Table: Resorts

Table: Rooms (linked to Resorts)

Table: Booking (linked to a person, room, and has a start/end date)

 

 

Your points system:

 

Table: Seasons (consisting of a point amount)

Table: SeasonalPeriod (linked to a Season, has a start/end date)

 

 

At this point you will have the ability to create bookings for a room for a given date range. The trick then is to tally up the points given the matching seasonalPeriod records that coincide with the booking period.

 

 

As an example:

 

I create a booking for myself for Room 3 at Resort A for the date range 01 December to 31st December (I like a long holiday)...

 

There are THREE seasonalPeriod records that are going to fall within my date range:

 

SeasonalPeriod X : 30 November - 10 December

SeasonalPeriod Y : 11 December - 20 December

SeasonalPeriod Z : 21 December - 31 December

 

Each of these coincide with a Season record.

 

---------------------

 

This is the way I would approach the issue. I'm not sure what you are meaning when you are wondeirng how to calculate if a date is a saturday/sunday - do these have different point values for each season, as does weekdays?

 

The actual calculations/relationship setup involved is beyond the scope of my response, I could figure it out but admittedly it would take some time, its not an easy solution by any means, but once in place I"m sure it would make your life much easier.

 

if Im bored sometime I might come back and see if I can't nut this out a bit further, and feel free to reply, I'll check back here often.

[iankh]

Hi Iankh

 

Here is what I'm thinking, keep in mind I may be off track here because my reasoning above might be wrong:

 

 

 

Your basic resort/room structure:

 

Table: Resorts

Table: Rooms (linked to Resorts)

Table: Booking (linked to a person, room, and has a start/end date)

 

 

Your points system:

 

Table: Seasons (consisting of a point amount)

Table: SeasonalPeriod (linked to a Season, has a start/end date)

 

 

At this point you will have the ability to create bookings for a room for a given date range. The trick then is to tally up the points given the matching seasonalPeriod records that coincide with the booking period.

 

 

As an example:

 

I create a booking for myself for Room 3 at Resort A for the date range 01 December to 31st December (I like a long holiday)...

 

There are THREE seasonalPeriod records that are going to fall within my date range:

 

SeasonalPeriod X : 30 November - 10 December

SeasonalPeriod Y : 11 December - 20 December

SeasonalPeriod Z : 21 December - 31 December

 

Each of these coincide with a Season record.

 

---------------------

 

This is the way I would approach the issue. I'm not sure what you are meaning when you are wondeirng how to calculate if a date is a saturday/sunday - do these have different point values for each season, as does weekdays?

 

The actual calculations/relationship setup involved is beyond the scope of my response, I could figure it out but admittedly it would take some time, its not an easy solution by any means, but once in place I"m sure it would make your life much easier.

 

if Im bored sometime I might come back and see if I can't nut this out a bit further, and feel free to reply, I'll check back here often.

 

Thank you for replying.

 

Yes, you understand the basic gist. The further complication is that Saturday and Sunday have different values from weekdays.

 

This is a very tricky problem.

 

My current system works, because every day of the year, for every room, type, for every resort has a day record with the points for that day/resort/room type.

 

This made the tally easy, but the table is growing out of control. with my latest update the table had over 90,000 records. The Mac runtime version for a compressed download is up to 47 MB. Uncompressed it's about 150 MB. The size is due to this points table that has become a monster.

 

That's why I really need to find some alternate solution, that will simplify and reduce the size of the points table, but will probably also significantly increase the complexity of the calculation.

 

Thanks again for your help.

[JFMiller3]

Kepping with Weebix structure,

 

Are the points of Saturday and Sunday related to seasonperiod points?

You could write a calculation based on the day of the week. ie:

IF(dayofweek(bookingdate)= 7 or dayofweek(bookingdate)= 1;bookingdate + x; bookingdate)

 

Take this 1 step futher. Create one point system based on the seasonperiods. Add multipliers to the destinations

 

New York City might have a multiplier of 3

Waseca MN might have a multiplier of .25

[Weetbicks]

Hi Ian,

 

well the way I see it its like this ( an example):

 

10 resorts x 30 rooms per resort = 300 rooms

300 rooms * 365 days = 109,500 records + 10 resort records = 109,510

 

 

The alternative:

 

still need 300 room records and 10 resort records = 310

 

But you could split into Seasons/Weekdays/Sat/Sun

 

 

= 5 season records with points for weekday/sat/sun in fields (could even go as far as 7 fields 1 for each day of the week) -

 

Whicih will give you 5 records * max 7 fields = 35 max point values

 

The rest is the complex stuff But you are right this would significantly cut down on the # of records !

 

I might have a think about this on the weekend and see if I can't figure out a nice solution to this problem for you. I think its probably going to require some fancy calculatitons and locator key fields for the dates and stuff, but anything is possible !

[iankh]

Hi Ian,

 

well the way I see it its like this ( an example):

 

10 resorts x 30 rooms per resort = 300 rooms

300 rooms * 365 days = 109,500 records + 10 resort records = 109,510

 

 

The alternative:

 

still need 300 room records and 10 resort records = 310

 

But you could split into Seasons/Weekdays/Sat/Sun

 

 

= 5 season records with points for weekday/sat/sun in fields (could even go as far as 7 fields 1 for each day of the week) -

 

Whicih will give you 5 records * max 7 fields = 35 max point values

 

The rest is the complex stuff But you are right this would significantly cut down on the # of records !

 

I might have a think about this on the weekend and see if I can't figure out a nice solution to this problem for you. I think its probably going to require some fancy calculatitons and locator key fields for the dates and stuff, but anything is possible !

 

Yes! I think you understand.

 

It is difficult because a reservation could traverse seasons, and then there are the weekends.

 

You also precisely understand my problem with the growing database. It is rapidly getting out of control.

 

I can't thank you enough for even looking at this problem. I am not a developer. I was only a really good Excel user a few years ago before I started this whole project.

 

You can see the runtime for yourself by going to http://www.mousemanager.com and going to the downloads link.

 

When you install, on the preferences screen you want to indicate that you are a DVC owner, that will give you the presentation fo the system where all this is applicable. There's some other set up that needs to be done that's outlined in my "getting started" document also on the downloads page.

 

Any insight you can provide would be most helpful.

[JFMiller3]

I am intrigued by this solution. I feel you need to simplify or you are going down the same road of duplicating 365 records of points each year.

 

I rethought my senario.

 

1st part

You need combine your points and seasons

Each season will will have 7 point fields (one for each (dayofweek)and a start and end date of each season.

 

You will have a record per season in this table. (5)

 

Your relationship to the schedule date will be:

 

Schedule date >= Season PointTable::Season start date

and

Schedule date

 

Schedule date points calc

Case(dayofweek(schedule date) =1; Season PointTable::point field one;dayofweek(schedule date) =2; Season PointTable::point field two;...dayofweek(schedule date) =7; Season PointTable::point field seven;"")

 

2nd part

 

You need a multiplier per resort

I will use disney properties as an example.

Pop century or All Star would be 1

Port Orleans might be 2

Grand Froridian could be 4

(you will need to crunch your point structure to get the multipliers)

 

This system gives you the flexibility to change the property points per multiplier (if the quality changes) also the ability to change the 35 point fields every year.

 

possible 3rd part

 

You can also bump this out one more level by assigning a multiplier to the customer. If the customer is a "premium member" they could have a multiplier of .75 compared to 1 for the average customer.

[Weetbicks]

^ Agreed. Multiplies would be awesome as every resort/room could have their own point value, but they are all determined from a base set of points depending on season/day of week. The only issue here is are multipliers even required, is every single resort the same in terms of points (I doubt they would be?)

[JFMiller3]

^ Agreed. Multiplies would be awesome as every resort/room could have their own point value, but they are all determined from a base set of points depending on season/day of week. The only issue here is are multipliers even required, is every single resort the same in terms of points (I doubt they would be?)

 

 

Ultimately that is the point. He might need to change the point structure. To add flexibility, He could add qualifiers for types of rooms.

Standard =1

Premium = 1.25 etc.

 

To continue his unwieldly process, he could anchor his point structure in place to dayofyear.

[iankh]

The multiplier is intriguing, but Disney sets the points value for each resort. That is hard and fast and never changes. The only changes that happen from year to year is an adjustment for the date ranges of each season.

 

So, for instance, at Old Key West Resort, a studio unit during Adventure season will always be 8 points per weekday night and 12 points per weekend (Friday and Saturday) night.

 

What will change are the start and end dates of Adventure Season, and there are multiple Adventure seasons per year.

 

This provides a level of stability.

 

The suggestion for the table design makes perfect sense.

 

The way I generate the thousands of point records today is with a little application that someone wrote for me.

 

I have an XML file that has the nested relationships. I edit the year and the start and end dates in the XML file, and then use this little app to generate .CSV files. It generates 1 row for each day of the year for the resort/roomtype.

 

I then import this .csv file into my points table.

 

I'm still a little stuck on the table relationship (the fields that would be related) and the summing.

[JFMiller3]

The multiplier is intriguing, but Disney sets the points value for each resort. That is hard and fast and never changes. The only changes that happen from year to year is an adjustment for the date ranges of each season.

 

So, for instance, at Old Key West Resort, a studio unit during Adventure season will always be 8 points per weekday night and 12 points per weekend (Friday and Saturday) night.

 

What will change are the start and end dates of Adventure Season, and there are multiple Adventure seasons per year.

 

This provides a level of stability.

 

The suggestion for the table design makes perfect sense.

 

The way I generate the thousands of point records today is with a little application that someone wrote for me.

 

I have an XML file that has the nested relationships. I edit the year and the start and end dates in the XML file, and then use this little app to generate .CSV files. It generates 1 row for each day of the year for the resort/roomtype.

 

I then import this .csv file into my points table.

 

I'm still a little stuck on the table relationship (the fields that would be related) and the summing.

 

This sheds a little more light on the subject.

 

How do you get your data from Disney?

 

Do you get 10 seperate point values per resort room type( 5 seasons and weekday and weekend)?

 

I would work to get your point table and season schedule combined. The first time you do this, it will take some time. Every year after, you could just adjust the season dates.

[iankh]

This sheds a little more light on the subject.

 

How do you get your data from Disney?

 

Do you get 10 seperate point values per resort room type( 5 seasons and weekday and weekend)?

 

I would work to get your point table and season schedule combined. The first time you do this, it will take some time. Every year after, you could just adjust the season dates.

 

Disney publishes the point charts every year. I usually get them in printed form before they're put on line.

 

I then go into the XML file that was created for me, and do some editing of years and season dates. The points remain the same. The only data that changes are the years and start and end date of each season.

 

I'm thinking that the XML actually does have the nested structure, that might be a starting place for the natural relationship of the data. The structure of the XML is currently

 

Resort

Year

Season

Roomtype (start date, end date, weekday points, weekend points)

[JFMiller3]

I am intrigued by this solution. I feel you need to simplify or you are going down the same road of duplicating 365 records of points each year.

 

I rethought my senario.

 

1st part

You need combine your points and seasons

Each season will will have 7 point fields (one for each (dayofweek)and a start and end date of each season.

 

You will have a record per season in this table. (5)

 

Your relationship to the schedule date will be:

 

Schedule date >= Season PointTable::Season start date

and

Schedule date

 

Schedule date points calc

Case(dayofweek(schedule date) =1; Season PointTable::point field one;dayofweek(schedule date) =2; Season PointTable::point field two;...dayofweek(schedule date) =7; Season PointTable::point field seven;"")

 

2nd part

 

You need a multiplier per resort

I will use disney properties as an example.

Pop century or All Star would be 1

Port Orleans might be 2

Grand Froridian could be 4

(you will need to crunch your point structure to get the multipliers)

 

This system gives you the flexibility to change the property points per multiplier (if the quality changes) also the ability to change the 35 point fields every year.

 

possible 3rd part

 

You can also bump this out one more level by assigning a multiplier to the customer. If the customer is a "premium member" they could have a multiplier of .75 compared to 1 for the average customer.

 

 

To keep the size of your file down,

 

Work to relate the point structure to the 5 season as above rather than to every day of the year.