Create a historical of data in MySQL

Question

Which is better way to create a historical of data in MySQL database ?

I have a table of Project, Task, Activity and Phase, and every time when update some data, need to create a historical for each change. In fact, I was created a ProjectManager to manager the Project, Task, Activity and Phase. Because I have these links:

One Project is composed by many Task, Task is composed by many Activity and Activity is composed by many Phase.

So, I don't know if create, for exemple, a Historical_Phase,a Historical_Activity, Historical_Task and Historical_Projet is the better way for my problem ?

Or a ProjetMediator_Historical is better?

I never worked with this, anyone can help me, please ?

See the ProjectMediator relashionships code in below :

public class ProjetMediator implements Serializable {

    private static final long serialVersionUID = 1L;

    public static final String FIND_PROJECT_BY_ID_PROJECTMEDIATOR = "ProjetMediator.findProjetMediatorByIdProjet";

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;

    @OneToOne
    @JoinColumn(name="id_projet")
    private Projet projet;


    @ManyToMany( targetEntity=Tache.class, fetch=FetchType.LAZY )
    private List<Tache> taches;

    @ManyToMany( targetEntity=Activite.class, fetch=FetchType.LAZY )
    private List<Activite> activites;

    @ManyToMany( targetEntity=Phase.class, fetch=FetchType.LAZY )
    private List<Phase> phases;
       //get and sets
}

Thank you.

http://stackoverflow.com/questions/17052488/best-practices-with-historical-data-in-mysql-databse/17053042#17053042 — Neville Kuyt, Jun 14 '13 at 09:58
Duplicate of [Database design for Revisions](http://stackoverflow.com/a/17002243/684229) — Tomas, Jun 14 '13 at 09:58
@Tomas I update with more details, please check, the links don't have a good answer to my problem I guess. Thank you! — Andriel, Jun 17 '13 at 14:00
@NevilleK I update with more details, please check, the links don't have a good answer to my problem I guess. Thank you! — Andriel, Jun 17 '13 at 14:00

score 0 · Answer 1 · answered Jun 14 '13 at 09:57

0

I'd create a history table that's partitioned by date (week, month, quarter, year) and I'd INSERT into it with triggers. That way you can move off old partitions as your archive and purge strategy.

All transactional tables could share that schema. I'd prefer that to having to create dual schemas for history and transactions. The transactional schema might not be best for analysis (e.g. star schema for the latter).

answered Jun 14 '13 at 09:57

duffymo

305,152
44
369
561

I update with more details, please check, the links don't have a good answer to my problem I guess. Thank you! – Andriel Jun 17 '13 at 14:01

Create a historical of data in MySQL

1 Answers1