I need an opinion from all of you about the table design for production transaction database (very heavily used approx 2.5 million inserts). I've attributes table and master table which stores column index:
TX_LOT_ATTRIBUTE (TRANSACTION TABLE)
UNTIL COLUMN_100 AT THE MOMENT. And I have another table called LU_METADATA, that's where I keep the column reference(Column ID, Column Name, Column Description, DataType, etc.. etc..). Everytime, the application needs to look for master data (which is in different database) and put the values into the correct column for transaction.
So, currently it is completely de-normalized table structure, I would like to re-design by keeping normalization in mind, i.e. something like this:
LOT_NUMBER + ATTRIBUTE_NAME will have primary key.
This is a pure transactional database, we have seperate database for reporting. Can you help me to justify based on your own experience which one will be faster for insert and update.
Your opinions will be highly apprecaited.