Hi Guys,
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)
=============
LOT_NUMBER VARCHAR2(13)
COLUMN_01 VARCHAR2(50)
COLUMN_02 VARCHAR2(50)
COLUMN_03 VARCHAR2(50)
COLUMN_04 VARCHAR2(50)
COLUMN_05 VARCHAR2(50)
========================
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 VARCHAR(13)
ATTRIBUTE_NAME VARCHAR(10)
ATTRIBUTE_VALUE VARCHAR(50)
=======================
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.
Regards
YamihO
Our community of experts have been thoroughly vetted for their expertise and industry experience.