Hi Everyone

I am having a problema whith hibernate to make a database that save the history of all records

Example:

a table named "student" whith the colunms

student_id name initial_date final_date pk(couser_id initial_date)

when a user insert a new student, the table looks like that

1 Jhon 2007-12-10 10:40:30 9999-12-12 23:23:59 1 2007-10-10 10:40:30

when the user update the row I update the table and maintain the history this way

1 Jhon 2007-12-10 10:40:30 2007-12-10 10:40:39 1 2007-10-10 10:40:30

1 Jhon 2007-12-10 10:40:40 9999-12-12 23:23:59 1 2007-10-10 10:40:30

My problem is that my code is getting too complicated, I cant find a pattern or a exemple showing a better way to implement this

When I insert a register that have a relation whith a record(s) in another table I have to insert a new record in all those other tables to maintain the consitency in the database and close the close that has been updated.

For example if the table "student" is associated whith the table "course"

When I change the course I have to update the table student too, this way

course_id intial_date final_date desciption

1 2007-10-10 10:40:30 2007-12-10 10:40:41 math
1 2007-12-10 10:40:42 9999-12-12 23:23:59 geology

update the student

1 Jhon 2007-12-10 10:40:40 2007-12-10 10:40:41 1 2007-10-10 10:40:30

1 Jhon 2007-12-10 10:40:42 9999-12-12 23:23:59 1 2007-12-10 10:40:42

Anyone has a better way or a pattern to implement this

I have already read all Martins Flower Pattern and didnt find a better solution to my problem

thanks in advance

The solution depends on what you want to do with the historical data. If it is there for reference only (i.e. no business logic operates around it), then you would probably be better off storing it in a separate table. You can either use a trigger to create the record in the history table, or map the history table with NHibernate and create/persist the records manually.

If you need to perform business logic on the historical data, you should start by normalising it. That is, I would consider having a Customer table and then a CustomerTransaction table (one-to-many). You would then just add a new transaction each time the customer is updated. This of course depends on how many transactions you expect to see for a single customer (for performanc reasons).

It is hard to suggest a solution without more details around how the business logic is structured around the data and the volumes of data you are expecting.

I need to perform business logic on the historical data.

If the system user need make a historical process, the user only have to inform the date and the system will make process whith all the information that is valid for the date the user inform.

I am using the fields initial_date final_date to know by example, if the client is valid for the date the user inform

Your solution to make the transaction table, is to create a table whit the id and another with the transaction like this

Client
ID

ClientTransaction
name, initial_date, final_date

is that?

to save the data in my system i am using this logic, but i am very unhappy with the result, the system works but i think it isnt a good solution

Here is the method to save a object called LayoutColeta

public void salvarLayoutColeta()
{
int codigoLayout = 0;

IList<LayoutColeta> listaLayoutColetaAtualizado = new List<LayoutColeta>();

try
{

DataTable datatable = this.datatable.GetChanges();

//Se existir diferenças
if ( datatable != null )
{

foreach (DataRow datarow in datatable.Rows)
{
switch (datarow.RowState)
{
case DataRowState.Added:

listaLayoutColetaAtualizado.Add(new LayoutColeta(0,
DateTime.Now,
DateTime.MaxValue,
(string)datarow["NomeLayout"],
(int)datarow["Posicao"],
"teste",
null,
null));

break;

case DataRowState.Modified:

codigoLayout = ((LayoutColeta.LayoutColetaKey)datarow["LayoutColetaKey"]).CodigoLayout;

IList<Caracteristica> teste = new List<Caracteristica>();

foreach (Caracteristica caracteristica in (IList<Caracteristica>)datarow["Caracteristica"])
{
teste.Add((Caracteristica)caracteristica.cloneBySerialization());
}

//(IList<Bem>)datarow["Bem"]


listaLayoutColetaAtualizado.Add(new LayoutColeta(((LayoutColeta.LayoutColetaKey)datarow["LayoutColetaKey"]).CodigoLayout,
DateTime.Now,
DateTime.MaxValue,
(string)datarow["NomeLayout"],
(int)datarow["Posicao"],
"teste",
teste,
null));


foreach (LayoutColeta layoutColeta in this.layoutsColetaBase)
{
if (codigoLayout == layoutColeta.LayoutColetakey.CodigoLayout)
{
layoutColeta.DataFim = DateTime.Now;
}
}
break;



case DataRowState.Deleted:

codigoLayout = ((LayoutColeta.LayoutColetaKey)datarow["LayoutColetaKey", DataRowVersion.Original]).CodigoLayout;

foreach (LayoutColeta layoutColeta in this.layoutsColetaBase)
{
if (codigoLayout == layoutColeta.LayoutColetakey.CodigoLayout)
{
layoutColeta.DataFim = DateTime.Now;
}
}

break;

}
}

layoutColetaDao.salvarLayoutColeta(listaLayoutColetaAtualizado);

this.setupViewFromModel();

layoutColetaView.showMensagemSucesso("LayoutColetaSucessoAtualizar");

}


}
catch (InvalidCastException)
{
layoutColetaView.showMensagemErro("LayoutColetaPosicaoNull");

}
catch (LayoutColetaException exception)
{
layoutColetaView.showMensagemErro(exception.Message);

log.Error(Mensagem.retornaMensagem(exception.Message), exception);
}
catch (Exception exception)
{
layoutColetaView.showMensagemErro("LayoutColetaErroAtualizar");

log.Error(Mensagem.retornaMensagem("LayoutColetaErroAtualizar"), exception);
}

}

here is the dao class

public void salvarLayoutColeta(IList<LayoutColeta> layoutsColeta)
{
ITransaction transaction = null;

CaracteristicaDao caracteristicaDao = new CaracteristicaDao();

try
{
transaction = session.BeginTransaction();

foreach (LayoutColeta layoutColeta in layoutsColeta)
{
//Se nao tiver um codigo, cria um codigo a partir da base de dados
if (layoutColeta.LayoutColetakey.CodigoLayout == 0)
{
//Verifica se o nome do layout coleta ja existe
Object quantidade = 0;

quantidade = session.CreateQuery("select count(*) from LayoutColeta as layoutColeta where layoutColeta.NomeLayout = :nomeLayoutColeta").SetParameter("nomeLayoutColeta", layoutColeta.NomeLayout).UniqueResult();

if (Convert.ToInt32(quantidade) > 0)
{
throw new LayoutColetaException("LayoutColetaNomeJaExiste");
}

//Seleciona o maior valor de codigo de processo no banco e soma um para criar o codigo do processo a ser salvo
object maxCodigo = session.CreateQuery("select max(layoutColeta.LayoutColetakey.CodigoLayout) from LayoutColeta as layoutColeta").UniqueResult();

if (maxCodigo == null)
{
layoutColeta.LayoutColetakey.CodigoLayout = 1;
}
else
{
layoutColeta.LayoutColetakey.CodigoLayout = (int)maxCodigo;
layoutColeta.LayoutColetakey.CodigoLayout++;
}
}

session.Save(layoutColeta);

if (layoutColeta.Caracteristica != null)
{
foreach (Caracteristica caracteristica in layoutColeta.Caracteristica)
{
caracteristica.LayoutColeta.LayoutColetakey.DataInicial = layoutColeta.LayoutColetakey.DataInicial;
caracteristicaDao.atualizaCaracteristicaAtual(caracteristica);

caracteristica.DataFinal = layoutColeta.LayoutColetakey.DataInicial;
}
}
}

session.Flush();

transaction.Commit();
}
catch (Exception exception)
{
transaction.Rollback();

log.Error(exception);

throw;
}
}

because Layout Coleta have a relation whith Caracteristica, i need to save a new caracteristica with the new initial_date of layoutColeta to maintain the consistency of the database

that´s my problem, i am looking for a better solution then if you save object A that is relationed to object B, i need to save object B again to update the FK between the two objects, since my pk is the id of the object plus the initial date

thank in advance

I've been working with similar problems, and have found this book very useful. It's out of print, but available in pdf form.

http://sql-info.de/sql-notes/developing-time-oriented-database-applications-in-sql.html