DB Design. Tracking history without losing referential integrity

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.

Replay

Category: database design Time: 2016-07-30 Views: 0

Related post

iOS development

Android development

Python development

JAVA development

Development language

PHP development

Ruby development

search

Front-end development

Database

development tools

Open Platform

Javascript development

.NET development

cloud computing

server

Copyright (C) avrocks.com, All Rights Reserved.

processed in 1.228 (s). 13 q(s)