storing deleted appoitments
I am building a booking app(appointments).
There will be times where the admin/user will want to delete an appointment.
I decided that these are better to be stored in a table and not removed completely.
There is a problem though that complicates the above and this has to do with the structure of the appointments table.
Each appointment has some services associated with it(selected by the person that close the appointment-the client) and these are kept in a separate table which is linked with a foreign key with the appointments table...here is the structure of these:
CREATE TABLE `appointments` (
`apID` int(11) unsigned NOT NULL AUTO_INCREMENT,
`Bookfrom` varchar(45) DEFAULT NULL
`bookedfor` mediumint(11) unsigned DEFAULT NULL
`appont_close_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`startDate` int(45) NOT NULL,
`endDate` int(45) NOT NULL,
`apps_origin` enum('frontend','backend') NOT NULL,
`staffID` int(11) unsigned DEFAULT NULL,
PRIMARY KEY (`apID`),
KEY `fk_appointments_user1_idx` (`Bookfrom`),
KEY `bokkedfor` (`bookedfor`),
KEY `fk_appointments_staff1_idx` (`staffID`),
CONSTRAINT `appointments_ibfk_2` FOREIGN KEY (`bookedfor`) REFERENCES `users` (`user_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8
CREATE TABLE `appoint_servi_chosen` (
`app_ID` int(11) unsigned NOT NULL,
`service_ID` int(11) NOT NULL,
PRIMARY KEY (`app_ID`,`service_ID`),
KEY `fk_appoint_servi_chosen_services_list1_idx` (`service_ID`),
CONSTRAINT `appoint_servi_chosen_ibfk_1` FOREIGN KEY (`app_ID`) REFERENCES `appointments` (`apID`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `fk_appoint_servi_chosen_services_list1` FOREIGN KEY (`service_ID`) REFERENCES `services_list` (`serviceID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
My intention is that in the table where the deleted appointments are going to be held there will be a column that will hold the services associated with it...VARCHAR
What do you think?