We help IT Professionals succeed at work.

Need everyone opinion

yamiho
yamiho asked
on
200 Views
Last Modified: 2010-04-17
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

Comment
Watch Question

This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.