I have to make 25 modification historic tables from 26 tables. I need a design for each table that could grow more than 75,000,000
of rows.
I use InnoDB
for 25 tables and 1 with MyISAM
. I have one design in mind but I want to see other choices vs what I got.
My company uses Birt
as BIT(business intelligence tools)
and uses historic logs a lot.
Legend:
OTPK
: Original Table Primary Key.HTNAME
: Historic Table Name.IDX
: It has an index.
My design tables:
ChangeInfo
:
- ChangeID INT(3) (PK)
- ChangeName VARCHAR(75)
HTNAME_LOG
:
- OTPK_Id` INT (PK)
- ChangeTime DATETIME (PK)
- ChaValue VARCHAR(255) (
Varchar value of the OTPK
) - IntValue BIGINT(15) (
Int value of the OTPK
) - ChangeID INT(3) (PK) (
Change Info PK
) - DataType BIT(1) IDX (
If true, integer(IntValue), varchar(ChaValue). This is for reading queries
)
PD: All the datatypes depend of the Original Table.
Any suggestion will be appreciated.