Database verification (Historical tables)

Last Post 28 Mar 2011 06:15 AM by Peter Schmitz. 4 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
river1
New Member
New Member

--
25 Mar 2011 08:11 AM
Hi Masters,

   Some times i receive some VB apps and their databases to look at.
 
My boss asked my to study a database and see if we can improve it.

I have see that this database uses Historical tables where data is stored (only for some tables).

They have the prin***l table and the historic table.

E.g -   I have a table named Log_session which logs the date that the user entered in the app and the date of living the app.

Here is the table structure:


ID;
CODFUNC;
DIN;
DOUT;

This table as a foreigh key to the employees table, where all the employees of the company are inserted.
The foreigh key is on CODFUNC which is primary key on table emplooyees.

Until now, everything is alright

Then i have a second table (historic table) with this name and structure:


name LogSession_Hist

ID;
CODFUNC;
DIN;
DOUT;
 
The VB app, when there is more that 1 month in the log_session table it removes (deletes all the records with more that one month from this table and pass it to the LogSession_hist table).

This is because of the time to load some grids in VB.
 

This data will go out from the original table Log_session and is inserted into the logsession_hist table.



My question is:


The person that designed the database did not put a relation (foreigh key) between the field CODFUNC of the table LogSession_Hist and Employees table.

BUT he put this relation in the original table (log_session). 


Would you create the relation betweeen the two tables (logsession_hist and employees) even knowing that this table is only to store data that can no longer be seen by the app interface?


I think it is good practice to put, because every table in a database that can be connected (related) to others to enforce referencial integraty  must use that connection (relation).

Some people say that it is not necessary because the relation is already on the original table .....

What do you masters think about this? 



















river1
New Member
New Member

--
25 Mar 2011 08:51 AM
Same happens (one have foreigh keys and the other not) with other two tables.

The tables are:

Model and H_model


Table Model as the following structure:

ID_MODEL;
CODRF;
Name;
,.......
......
.......

This table as all the inserted models in IT.

Then we have other table, called h_model.

This h_model table stores every change that is made to a model.

The table model only has the last update to the model record.


For example: Supppose i insert a model on table models which it's stored as:

1;
1;
Pedro;
,.......
......
.......


Then , through the VB app i change the name of the model from Pedro to Paul. Then the table model would update the name to Paul and the model record would be in the table as:

1;
1;
Paul;
,.......
......
.......


Because there was a change in the table model, the change is stored in the h_model table, so this table would have two registers:

This:

1;
1;
Pedro;
,.......
......
.......

And this:

1;
1;
Paul;
,.......
......
.......

What is the problem here? well it's the same, i don't know if i should create relationships between h_model table and other tables as they exit to model table.

Model table as a foreigh key in CODRF eith the table RF.

Should i create this realtionship in H-model too?

Some people say not too because unnecessary connections (relations between tables) only cause slow down to databases. I think that they are necessary to garanty the integrity of the database. What do you masters think?









river1
New Member
New Member

--
25 Mar 2011 08:52 AM
Note - Sorry to have so much text in the message but it was the only way to explain my doughts on which i should or not create foreigh keys on Historic tables.



river1
New Member
New Member

--
25 Mar 2011 09:00 AM
P.S - the structure of the H_model table is:

ID;
DateH;
CODFUNC;
ID_MODEL;
CODRF;
Name;
.......
......
......

It as tree new fields
(ID;
DateH;
CODFUNC) to store the date and the person ho did the update to the original record that is on table model.

I was thinking in create a foreigh key in h_model in CODRF as it exits in the Model table to the same table ( RF)
So, h_model would be related to table RF as model table is...

I was thinking in creating too a relation nship between H_model and Model tables on ID_model, because for a model to exit in the h_model table it as to exist in the model table.
Peter Schmitz
New Member
New Member

--
28 Mar 2011 06:15 AM
The only thing I would change is I would move the archiving task from the vB app into the database layer, and have it done by a scheduled task.

As for the relationships, I would personally leave them off, purely based on your question of "would I personally touch them or not?". If the original INSERTS passed the business logic test, additional checks on the historic tables will only slow things down when the move happens. Unless you have very solid business reasons for enforcing the relationships on the history tables as well, it's not broken, so why look for a possible fix?

The original developer might have had good reasons for building the system this way, and unless the business specifically calls for it (a bug, or business requirements), you might end up breaking something by adding additional keys.

Just my 2 cents.


Acceptable Use Policy
---