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 Customers 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 dont 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