Advise required for database structure

[Rattlesnake]

Hi, I am working on a Sales Order Confirmation ASP.net application (Sql Server 2000). In this application there is an Excel file that has to be uploaded every month that contains the Sales FORECAST given by all our Customers for the next 12 months.

 

It has the following format (these will be the columns of the excel sheet)

CustomerId

ProductID

Year

Jan

Feb

March

April

...

...

Dec

 

In the JAN to DEC columns the Customer specifies its forecast for that month.

 

I would like to know the best Database structure that I should use for importing this excel file. The easiest way would be

 

create a table with a structure similar to the excel file, but what I will require later will be a lot of Queries that would

 

group on ProductID and Customer ID. Reports like the Customer's FORECAST vs Actual Orders per month, trend for a Product for

 

the past 3 months etc.

 

I would like your suggestion on the IDEAL Table structure to use and how to IMPORT the Excel file into that table structure.

 

My idea was something like this

 

Master Forecast Table

---------------------

ID

CustomerID

ProductID

 

Detail Forecast Table

---------------------

ID (foreign Key)

Month

Year

ForeCastQuantity

 

 

Thus each row from the Excel File, will have 12 rows (jan to dec) in the Detail Forecast Table.

 

Am I on the right track ?????

 

 

If this OK, what is the best way to convert the Excel file into this format???

[kejpa]

Just a quickie...

Why two tables? The only reason for the first table would be to create a unique key to use in the second, right?

Why not...

Forecast Table

---------------------

CustomerID

ProductID

Month

Year

ForeCastQuantity

 

/Kejpa

[Joe Mamma]

Just a quickie...

Why two tables? The only reason for the first table would be to create a unique key to use in the second, right?

Why not...

Forecast Table

---------------------

CustomerID

ProductID

Month

Year

ForeCastQuantity

 

/Kejpa

First, save yourself alot of trouble and make Month, Year one fields. . . a Date. You can force it to be the first day of the month, or the last, depending on what is appropriate. Dates are dates! treat them that way!

 

Second I could see a need for two tables. . . if there is anywhere else in the database where (CustomerID, ProductID) is a primary key, it makes sense to add a surrogate to that table and then use that surrogate key in the MonthlyForecast for that relationship. for example, lets say you also were trackingproduct inquiries such as Customer A requested info on Product 1 and you were keeping track of Customer support for a customer and Products such as JohnDoe supports Customer A on Product1 and MaryDoe supports Customer A on product 2. this would be the best schema:

note: Bold indicates primary key fields, Italic indicates surrogate key

 

[CustomerProduct]

CustomerID references (Customer)

ProductId references (Product)

CustomerProductID (unique)

 

[ProductInquiry]

CustomerProductID references (CustomerProduct)

DateOfInquiry

DateOfResponse

 

[ProductSupport]

CustomerProductID references (CustomerProduct)

EmployeeID references (Employee)

 

[SalesForecast]

CustomerProductID references (CustomerProduct)

ForecastDate

ForecastQuantity

 

Third, as far as unpivoting an excel spreadsheet I don't know of any automatic way.

 

you can open the spreadsheet using the Jet OleDb Provider.

 

psuedo code:

for i = 0 to 11

select CustomerID, ProductID, i Month, monthcolYear into aDataTable

 

 

add a ForecastDate column to aDataTable as a date time and iterate throught the table setting the ForecastDate based on Month and Year

 

now you got a datatable you can use to move to the SQL server

 

psuedo code:

foreach row in aDataTable

if not exist

select * from CustomerProduct

where CustomerProduct.CustomerID = aDataTable.CustomerID and

CustomerProduct.ProductID = aDataTable.ProductID

insert into CustomerProduct(CustomerID, ProductID)

values(aDataTable.CustomerID, aDataTable.ProductID)

 

foreach row in aDataTable

insert into SalesForecast

select CustomerProductID, aDataTable.ForecastDate,

aDataTable.ForecastQuanity

from CustomerProduct

where CustomerProduct.CustomerID = aDataTable.CustomerID and

CustomerProduct.ProductID = aDataTable.ProductID

Edited January 7, 2006 by Joe Mamma