Re: How to allow incremental versions of the data in a DB

[Ricardo Rodríguez]

I was reading your others responses about EAV. I don't know if that's my case. I would use a separated table for each attribute which I need to keep it's history, and not a single table that saves the values for all the attributes.

I'm using JPA, as I was reading, it could be a bit difficult to manipulate JSON. But, why do you think that's better to use JSON and increment the logical in the application code, implementing searching and sorting methods, than using EAV?

Anyway, now I'm thinking that I need to find a better solution than using those key-value tables, because in the future I'll need to add a table for each new attribute in LegalProcess table.


Below I paste the CREATE TABLE.


CREATE TABLE `LegalProcess` (
`id` int(11) NOT NULL auto_increment,
`deleted` bit(1) NOT NULL,
PRIMARY KEY (`id`)
);


CREATE TABLE `Survey` (
`id` int(11) NOT NULL auto_increment,
`dateStart` datetime default NULL,
`dateEnd` datetime default NULL,
`name` varchar(255) default NULL,
PRIMARY KEY (`id`)
);


CREATE TABLE `LegalProcess_FileNumber` (
`fileNumber` varchar(255) default NULL,
`LegalProcess_id` int(11) NOT NULL default '0',
`Survey_id` int(11) NOT NULL default '0',
PRIMARY KEY (`LegalProcess_id`,`Survey_id`),

KEY `FK1FB6240E18E02EC9` (`Survey_id`),
KEY `FK1FB6240EA017DA65` (`LegalProcess_id`),
CONSTRAINT `FK1FB6240E18E02EC9` FOREIGN KEY (`Survey_id`) REFERENCES `Survey` (`id`),
CONSTRAINT `FK1FB6240EA017DA65` FOREIGN KEY (`LegalProcess_id`) REFERENCES `LegalProcess` (`id`)
);