Transaction Log Normalization Question

[Brian]

I see a lot of normalization questions from this discussion group. May be
you can help to answer my table normalization question that I will need to
design in SQL 2005. If this is not the right area, please point to the right
place to post the question.

I have a daily settlement credit/debit card sales transaction data import
into a single table (1NF) table for user to perform balancing. This table
contains lots of data as we are adding daily sales data into the single
table. We are debating whether we should normalize it or not since it is a
daily settlement data and user always select a particular settlement date to
see the information. Is it necessary to normalize it since user will need to
see all the information in this single table?

Below is the fields in this single table:

Settlement Date,Transaction Date,FIID,Store,Terminal,Card Number,Expiration
Date,Amount,Response Code,Sequence Number,Invoice Number,Approval
Code,Merchant ID,Entry Mode,Cash Back,Tran Code

We can normalize this table to by separating the card number, expiration
date information and store, terminal information to another table. However,
is this beneficial to the user who needs to gather the transaction
information like above for balancing? Don't we still need to keep a table
with this transaction log for query?

Any advise would be appreciated.

Thanks,
Brian

 

[Jeff Boyce]

Brian

If I understand your question, that's what queries are for. You can build a
query that joins normalized tables and returns whichever fields you want.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[Brian]

Yes. Let's say we normalize the single table to different tables like,
Store, card number, response code respectively. Don't we still need to have
a transaction table to retrieve the information? I guess I need to know how
to define the transaction table after I normalize to different tables.

 

[Jeff Boyce]

Brian

Normalization is about determining the entities and relationships that
describe your data. If one of your "transactions" is a collection of
information about store, card number, response code, etc., then your
transaction table would have these data elements.

How you use forms to add/edit data in your tables is secondary to getting
the table structure in place.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/