Thread: Design help-can this be done?

Hi All,

Ok, I have something new to tackle and I'm not sure if / how it can be done so I wanted to some feedback.

Currently an excel spreadsheet is used and sent out to many different locations, they enter their forecast numbers for 9 mos. out and still the last month so a comparison can be done to display the difference. Once its sent back its combined into one spreadsheet that can be filtered by each individual location or by Region or ALL. On the spreadsheet 3mos. actuals numbers are displayed, so each month that has just past gets updated with actual numbers. so an example looks similar to below:

Description - jun09act- jul09act - aug09act - sep09fct - octfct - novfct - etc..9mos out.
desc names - 100 - 101 - 103 - 110 - 111 - 111 - 111 ...etc.

some other fields that will be calculated here where I will sum some items/description and divide by another value...etc.

One of my questions, do you suggest I sent up 2 tables, one for actuals and one for forecast because one person will want to dump actual numbers each month as they pass but forecast will be entered on the web interface by multiple users for their location?

Next question, should my table look like this? or should I just have a DateID field that holds mm/dd/yyyy?
RegionID
LocID
MonthID
YearID
Value

Next question, if my table is setup to hold a value for each month/year...how can i do this dynamically to where I can get the results displayed as needed? stored procedure? Also, would I be able to have a search screen to search by location and default to current month for forecasted part but have something to where if the user wanted to go back in time they can put in a different start forecast mo/yr?

Goodness....trying not to get overwhelmed with this.

Thanks for any answers here...

I would probably go with something like below....assuming that there are multiple locations per region.

Code:

Regions
RegionID | Region
 
 
Locations
LocationID | Location
 
 
LocationsInRegions
RegionID | LocationID
 
 
ForecastsAndActuals (not sure what exactly you're forecasting and putting actual values for, so a different table name would probably be used)
ForecastActualID | LocationID | EntryDate | Value | IsForecast

IsForecast would be a bit field which represents if the entry is a forecast or if it is an actual value.

Yes, with my design you could put as many values for each month as you need. There is no need to create a mo/yr record...just put the date in for the forecast/actual day. I wouldn't prepopulate the table with NULLs....simply populate the table when they are ready to enter the data.

No, I'm not saying that at all...it's a possibility, but how you do the UI design is irrelevant. I was saying that there's no reason to pre-populate the database with NULL when data hasn't been entered into the form.

Not sure what you mean by, "I think I'll separate the actual from the forecast since the power user will hand those values over..."

I did something like this a few employers ago. In my case, I split the Forecast and Actual data into two separate tables -- it just made working with them easier and (a) allowed me to display the report as of any month (historical reporting) and (b) allows for different authorization on the Forecast and Actual entries.

While visually it makes sense to think of the month/year data as columns in a spreadsheet, in relational database design it make more sense for the Forecast and Actual data to be records. Remember, it's really easy to add a new column each month in Excel, but its not something you want to do in a database -- there should never be a good reason to add columns as part of routine usage. Also, don't add lots of NULL values into a table unless there is a GOOD REASON for them -- a NULL should have some semantic meaning in the design.

JMH is right -- separate the UI from the database design -- how the data is physically stored in the database and how it is present to the user are too separate concept. Since this is a new project, concentrate on the DB design and not so much on the UI.

Originally Posted by Rebelle

Also, if I don't prepopulate table how will I be able to select a location then display empty results/then move to form to edit/enter value.

The 'empty' results would come from doing a LEFT JOIN from the location table to the forecast table. If there is no record for a given (Location,Month,Year) then a NULL result would be generated.

When it comes time them to start generating results for your UI, then consider using Views over the data tables to return the results in a form that is easily manipulated by your code. Views are a good way to denormalize the data for reporting purposes.