I have thousand of text files which store daily inventory info.
Each file is around 5 MBytes, so there's aound 7 GBytes of text data currently.
I want to sort them into database.

Each of the file stores a single day info, there are 1,000 products in a file,
each product may keep stock in 200 companies, each company got different product inventory.

If I just need to store just one day info, I can create a table
say 1000 row(product) x 200 column(company) got 200,000 cells which storing the inventory info.
It seems it's not properly for a table to contains 200 column, and my another problem
is how is the design to store more than 1 day info.

My target is, all the data will present in graphic type info, lines or bars,
and which can sort data in two ways: 1)trace the inventory trend of a product, or 2)trace the inventory trend of a company.

Anyone can help?

At this point, I have 2 plans:
1) Each day insert 200,000 records into a table, each record contain date/product/company/inventory,
so it will have 300,000,000 records up to now, sounds terrible... (or is it the usual case? )
2) It will have two tables(for tracing issues):
Table Product - each day insert 1000 records, each record contain date/product/[a string contain 200 company's inventory]
Table Company - each day insert 200 records, each record contain date/company/[a string contain 1000 product's inventory]
& it need do some programming to extract the string on-the-fly.

Is there any better solution? Thanks.

Can someone point me to the right examples?
I believed some of the companies should sort the similar type of data.

Here in short, I got daily inventory data 1000 x 200 x 1500 (product x company x days),
how can I sort it structurally and turn it into traceable graphical data?

Thanks again.

Finally I found the answer and it's related to the topic of:

BI- Business Intelligence / DW - Data Warehouse / ETL / OLAP...

Most the database solution suppliers got the product which can solve my problem, however, they all Enterprise grade & may difficult to familiarize with.

That is actually a whole lot harder than it needs to be...

Create three tables, the first table for products, the second table for companies, and the third table for inventory. The inventory table should have foreign keys to the product and the company tables, along with the date and the quantity.

Once you've done this, all you need to do is import the data into your tables, then build the graphs using either an application (like Microsoft Excel or Open Office Calc) or code that you build into your website.

-PatP

if you can graph it Excel, then you can graph it in Access, both applications use the same graphing tool.

Thanks Pat, it's really hard for a beginner, I have little experiance in MSAccess only.

My data structure seems got 3 axises, Product/Company/Date, forming a cube.
I found BI/DW solution exactly build for analysis multi-axises data.
It is charm, but not for me for my limited database knowledge.

I planned using SQL Server coming, follow your suggestion to create 3 tables.
My concerns still the database performance, the "third" table will got 300,000,000 records, and every time I try to analysis say, a product trend/averaging/..., it may need doing SQL SUM for 200 times cause there got 200 companies.

It's also my original plan to use Access to present the graph.
However, I planned migrate to SQL server due to huge data records, do you know what is the easy/best graphic apps to work with SQL server? For programming, I know Access & VB.net only.

(Microsoft seems got a new chart component for .NET, I haven't try it but I use a free module Zedgraph. It seems need to shift to Microsoft again for highly integration and speed up development.)

If you use Microsoft SQL Server, you'll get Integration Services and Analysis Services included on the same media. At that point you can choose between using Microsoft Access for a desktop application or SQL Server Reporting Services (again, included on the MS-SQL media) for a web application.

-PatP