Date Driven DB Design Help

[kimberlyb]

I am rewriting a database I started incorrectly and I'm stuck on trying to
move it to a "date driven" DB. Basically I am creating a DB to track
projects (maintenance items), actual revenue (invoiced), forecasted revenue
(sales target - which is everchanging based on purchase orders received), and
an "outlook proforma" (one time forecast that is not updated for trending).
That way when I write queries it can be from a date period instead of having
to write a query for every scenario we want to interrogate. Also, for ease
of data entry, I need data that is captured monthly (like a crosstab) however
I'm not sure how to assign a date to a record which would allow me to have
one record per project. I don't want the end user to have to select date
parameters. Can anyone help me???

Thanks

Kimberly

 

[Duane Hookom]

I'm not sure I understand your question. Three suggestions seem clear to me
is normalize, normalize, normalize. Having one record per project seems like
a huge block to creating a functional and flexible application.

 

[kimberlyb]

Its really hard to explain in writing how we collect data. Currently, we are
collecting in an excel spreadsheet where there is one row per project and a
group of columns that represent data for that project. For instance, Jan-07
ASP(avg. selling price), Jan-07 cost, Jan-07 quantity, etc. for every month
once the project has started. The company would like to keep the same "Front
End" the same so it is seamless for the end user to move from excel to
access. I have written the DB to collect this information in fields however
it limits the reporting flexibility. How can I keep the front end the same
for the end user and add the reporting needs. As you can tell I'm very new
at DB design and this particular project has grown into a monster with the
"needs" of the company. Thanks for your quick response.

Kimberly

 

[Duane Hookom]

I have seen some huge problems created by keeping the "look" of a previous
version. "look" should not drive table structures.

However, if you have all your information captured okay in your
un-normalized table, you can create a union query to normalize the data for
reporting purposes.

 

[Jamie Collins]

I am rewriting a database I started incorrectly and I'm stuck on trying to
move it to a "date driven" DB.

If you already 'speak' SQL, download the free PDF of "Developing Time-
Oriented Database Applications in SQL" by Richard T. Snodgrass:

http://www.cs.arizona.edu/people/rts/publications.html

Jamie.

--