I have a dilemma regarding how I should handle the following situation. I have a dynamic calendar script. The calendar script generates each month with the correct date sequence automatically. I have taken the date that is generated by the script and have restructured it to where it is in the yyyy-mm-dd format. This is how the dates are stored in the database for accurate date sorting.
I am developing a members only web site where each member creates an account and has access to their own information / calendar. In order for each member to update their own calendar, they go into what I will call "Edit Mode". Basically in edit mode, the calendar has checkboxes for each date. If the member checks the boxes in order to submit the dates of their choice and submits the information, the selected dates are submitted as individual records into the database. Therefore, if each member selects and submits all 365 days in the calendar (e.g. the entire year) and there are 10,000 members, that's a lot of records (3,650,000) just for the calendar! I currently have two fields that I am working with in the mysql table. They are... member_id (INT)(11 Characters) date (DATE)(0000-00-00) Currently, the process is that after the dates are submitted, they are entered into the database in date sequence as individual records. For Example: member_id date 10001 2007-01-01 10001 2007-01-02 10001 2007-01-03 10002 2007-01-01 10002 2007-01-02 10002 2007-01-03 If the member goes back into edit mode later and unchecks one of the boxes for the date selected, all of the dates for that particular member are deleted from the database and re-submitted all over again, minus the one date the member unchecked. Is there a better way to structure the database and minimize the number of records that are submitted by each member? Any assistance on this is greatly appreciated! Thanks in advance!
