Redesigning a relationship and calculations - tricky

[iankh]

I hope this is the right forum to post this question/problem.

 

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

 

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.

 

Also, doing the ultimate reservation TotalPointsForBooking field calculation has me stumped. 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 there is the lookup which could cross years and seasons and finally the ultimate TotalPointsForBooking.

 

Any suggestions would be sincerely appreciated.