I'm converting an existing appointment calendar to a DNN module. A single recurring appointment can often create hundreds or thousands of database records.

To avoid many thousands of database calls the existing application sends a single record to a Stored Procedure. This Stored Procedure contains the logic to split and save the recurring appointment into individual dates.

It is unlikely that we will ever use anything other then MSSQL so portability is not an big issue. Also the MSSQL database is located on a separate server so that may be a factor too.

My gut tells me to keep the logic in the SP but that goes against the concept of n-tier design.
I haven't been able to find anything that talks about exceptions to placing all the logic in the BLL so I thought I would ask your opinion before making a decision.

1. Why store thousands records in DB for recurring meeting?
I'm sure there must me elegant way to solve this with one record...

2. Using layer seperation increases your flexbility and reduces your performance.
For complex and large application i would always use DAL and BLL.
Remember that each DB call is taken from the connection pool and no
new connection is being opened.
To avoid to much DB call try use extensive and smart cache.

For example, hold in cache the calendar data of X popular users,
as long as they don't update their calendar - don't even make db call...
when they write new data to calendar - updaet the cache,
that way you almost never read calendar data from db , you just write.

Thanks for the input.
These calendars are used for large multi-group medical offices. The data changes continuously during business hours so caching isn't practical.

I split the recurring appointments when they are added for performance reasons. The appointment is added once but read many times. If someone is viewing the appointments for a given day the application doesn't have to re-evaluate every recurring appointment that could potentially fall on that date.

There are other advantages to separating recurring events too, such as conflict checking and the ability to modify a single date within a recurring set. The database is normalized so most of the appointment data is stored only once.

I think I found a good compromise to my problem. I will move the logic to the BLL but instead of making many calls to the database I will send a single comma delimited string to the database. The Stored Procedure will parse this string to create the records. BLL is happy and I can still use transaction processing in the Stored Procedure insure that the entire recurring set was added correctly.

Questions and comments welcome.