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.
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
BIT(business intelligence tools) and uses historic logs a lot.
OTPK: Original Table Primary Key.
HTNAME: Historic Table Name.
IDX: It has an index.
My design tables:
- ChangeID INT(3) (PK)
- ChangeName VARCHAR(75)
- 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.