creating a normalized database from a non-normalized flat file

[Guest]

Here's my situation, which I can't wrap my head around. I have a flat file
of non-normalized data. I want to dump the file into an Access db and run
reports on it but I understand that for performance reasons the data need to
be normalized.

This raises two questions:

1) What is the best way to accomplish the initial normalization?
2) I will be adding to this data from a similar non-normalized flat file
every month. How do I add subsequent month's data to the db, keeping in mind
the issue of normalization?

I realize these are fairly open-ended questions, and I understand the
concepts of data normalization. I'm just not clear on how to get from flat
file to normalized database structure, and maintain this on an ongoing basis.

Thanks for any thoughts,

Dave

 

[Arvin Meyer [MVP]]

I import my data into a temporary table, then run queries on it to move the
data to their respective tables. The field that you designate as the Primary
Key in the temporary table is included in every query that divides the data
into its repective tables.

Depending upon what you want to do with the data, if you only need simple
reports, leaving it as a flat file may be OK. If there are many more records
than you intend to use in your reports, you probably should normalize to
increase performance.

 

[Guest]

There will eventually be several hundred thousand records, so I sort of
assumed normalization is the way to go....

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.

I import my data into a temporary table, then run queries on it to move the
data to their respective tables. The field that you designate as the Primary
Key in the temporary table is included in every query that divides the data
into its repective tables.

Depending upon what you want to do with the data, if you only need simple
reports, leaving it as a flat file may be OK. If there are many more records
than you intend to use in your reports, you probably should normalize to
increase performance.

 

[George Hepworth]

This may be heretical to some, but if you are only going to use this file
for reporting purposes, and the only reason you are putting it into Access
to make it accessible for Access reports, then you probably DON'T need to
normalize it.

Keep in mind that this is a very unique case and is not to be taken as a
suggestion that normalization is not necessary for active databases where
you are inputting and editing data.

 

[Arvin Meyer [MVP]]

So let me give you an example of a non-normalized file. I once built a
database used by a large soft drink bottler. In the test market, we had
between 80,000 and 100,000 records every day being read from a group of 10
text files that were downloaded to a PC. The text files were flat as a
board. I used a partially normalized set of tables to build queries against
these text files and saved the summary (about 2,000 records a day) to a
table. I built a daily set of reports from the summary data. The original
data (the text files) was saved to the hard drive, but discarded from the
database.

The summary tables were normalized, but the original flat files were just
left the way they were.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

There will eventually be several hundred thousand records, so I sort of
assumed normalization is the way to go....

Dave