crucial problem about two tables in a database!!!!!!!!!!!!11
I have two tables that stores company information in a database. Since retrieving
data from the tables is too slow, the structure need to be improved. The current
structure of the tables is as follows:
First table contains
table1
companyNo : unique field that is created by sequence
companyTaxNumber:have a unique index
companyName : have an index, but not unique
companyAddress
companyCountry
timestamp
trigger on table1:
if insertion occurs: timestamp:0
if update occurs : increment timestamp by 1
Second Table Contains
table2
Up_companyNo2 : unique field that is created by sequence
Up_companyTaxNumber2:have a unique index
Up_companyName2 : have an index, but not unique
Up_companyAddress2
Up_companyCountry2
timestamp
trigger on table2:
if insertion occurs: timestamp:0
if update occurs : increment timestamp by 1
update table1 where table1.companyTaxNumber=table2.Up_companyTaxNumber2
and timestamp=1
the problem is the application uses table1 details, and give reference to
"companyTaxNumber" in forms. When the company information is updated (rows are not
updated, inserted with a new companyNo via sequence) and inserted into table1 from
table2, the new forms will use the updated information. However, old forms cannot
use updated ones, since reports cannot be changed.
in conclusion,
table2 contains last updated company information, the initial information before
updates are not stored in table2
table 1 contains all the updates as new entries since forms that use these entries
cannot change the references. So, table1 gets bigger and datas retrieved
slowly.
Can you give me clear advices about the improvement techniques about indexes and
triggers? How can I handle this situation?