To make it simple, let’s say we have these 2 tables:
CREATE TABLE `tbl_companies` (
`id` int(11) NOT NULL,
`name` varchar(100) NOT NULL,
PRIMARY KEY (`id`)
);
CREATE TABLE `tbl_workers` (
`id` int(11) NOT NULL,
`name` int(11) NOT NULL,
PRIMARY KEY (`id`)
);
And another 2 tables to store the relationship between companies and workers
CREATE TABLE `tbl_companies_workers` (
`company_id` int(11) NOT NULL,
`worker_id` int(11) NOT NULL,
PRIMARY KEY (`company_id `, `worker_id `)
);
CREATE TABLE `tbl_companies_workers_history` (
`company_id` int(11) NOT NULL,
`worker_id` int(11) NOT NULL,
`start` date NOT NULL,
`end` date DEFAULT NULL,
PRIMARY KEY (`company_id `, `worker_id `)
)
With this schema it’s easy to display something like: “John Smith worked at MyComp from 2010 to 2012”. But if MyComp is deleted from de DB, then that information is deleted too. I’d like to be able to display the same info even though the company is deleted.
Is it possible to achieve this without losing referential integrity? What would be the best approach? I've been looking for some info and I found this and this, but still it’s not clear to me.
Thanks in advance.