Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We have a table called tblLink which is the main table and have its primary key in many other tables as shown below. The issue now we want to keep only the latest 3 months data and the rest should be delete and archive. The one option to partition each individual tables separately based on dates. Is there any option or solution where based on the linkID in tblLink I do the partition together for the rest of the tables? Is partitioning the right way to move or is there any other options?

All these are InnoDB tables.

CREATE TABLE IF NOT EXISTS `tblLink` ( 
`linkID` int(5) NOT NULL, 
-- other columns
PRIMARY KEY (`linkID`) 
); 


CREATE TABLE IF NOT EXISTS `tblEmailLog` ( 
`emailLogID` int(11) NOT NULL AUTO_INCREMENT, 
`linkID` int(11) NOT NULL DEFAULT '0', 
-- other columns
PRIMARY KEY (`emailLogID`) 
); 


CREATE TABLE IF NOT EXISTS `tblEventAlert` ( 
`eventAlertID` int(11) NOT NULL AUTO_INCREMENT, 
`linkID` int(11) NOT NULL DEFAULT '0', 
-- other columns
PRIMARY KEY (`eventAlertID`) 
); 



CREATE TABLE IF NOT EXISTS `tblMainData` ( 
`mainDataID` int(11) NOT NULL AUTO_INCREMENT, 
`linkID` int(5) NOT NULL, 
-- other columns
PRIMARY KEY (`mainDataID`)
); 


CREATE TABLE IF NOT EXISTS `tblSubData` ( 
`subDataID` int(11) NOT NULL AUTO_INCREMENT, 
`mainDataID` int(11) NOT NULL, 
`linkID` int(11) NOT NULL, 
`eventAlertID` int(11) NOT NULL, 
-- other columns
PRIMARY KEY (`subDataID`) 
);
share|improve this question
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.