Dynamic database

[tractorvix]

I think I need to set up a database to which I can add extra columns and extra rows and then be able to manipulate this data. Basically I have a current list of products and am looking at forecasting sales for the next x years

but if i set the years up as columns, how do I view the data dynamically?

ie
Product 2008 2009 2010
A 10 20 30
B 5 4 2

so it's obviously easy to add in an extra product, but how do I go about adding in an extra year?

Do I need to set up as:
YrProd key Value
2008A 10
2008B 5
2009A 20
2009B 4
2010A 30
2010B 2

I have around 13 tables to set up and would like to get the basics right before I attempt to set up any calculations or dependencies. I currently have around 80 products (which could double in the next year) and am looking for forecast monthly for the next 5 years.

Once I get the table set up right, I think I should be able to set up the forms etc to allow users to view and amend the data contained within.

Any help would be greatly appreciated.

Thanks
Vix

 

[Remou]

Set up the years as a year field:

ProductID
ProductYearOrDate
Value

You can use a crosstab to show the data dynamically. The usual rule is that the database should be designed according to normal principles and the display will follow. Have you had a chance to read

http://www.r937.com/relational.html?

I would say that reading such a document is the first step to take.

 

[tractorvix]

Hi Remou,

thanks for the response. I've had a read through the MS DB normalisation, but will have a look through this one too. I need to be able to update the tables as well as display them, I don't think it's possible to update values in a crosstab view is it?

Is there any way I can display the data in a cross tab type view, but make it amendable by users?

Thanks again,

Vicky

 

[Remou]

Not without goining to some lengths, however, adding fields and controls on the fly would lead you to greater lengths. Is it necessary that the data should be editied in the crosstab? Surely past years would not generally be edited?

 

[MajP]

I am echoing everything that Remou has said. Adding fields on the fly is clear indicator of a very, very bad database design.

A crosstabe query for veiwing the data is the way to go. You could have this query as a subform with some buttons on the main form. The buttons could pop up another form for editing a specific record. This way you could have normalized table.

 

[SkipVought]

Vicky,

Do not fall into the trap of "dynamic" database. This is the amateur way of solving your apparent problem. It appears to be the best choice, but this approch is frought with all sorts of unforseen problems.

The really egghead database research guys get excited talking about first, second third form normalization etc, and your eyes begin to glaze over. But the reality is that there is a real price to pay for ignoring these best and accepted practices.

Pursue your "dynamic" database approch, and you'll be posting questions in the future regarding how to accomplish tasks what would be really simple stuff using a proper database design.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[MajP]

However, if you really need a "spread sheet" look for input and display you could simulate it, from your normalized table structure. This is how I would do that.


1)figure out how many years to show
2)build a table definition dynamically
3)Populate this table with column years and product rows
4)display table in a form. Write a little code to make this act like a spreadsheet so that you can move easily in the "cells"
5)On close of the form write this unnormalized data back into normalized tables

If interested in this, I could demo some code, but I would not recommend this unless you are vb savvy.

 

[Remou]

It is generally better to explain to management how it must be, rather than to commit spreadsheet, a crime much frowned upon. You will find page upon page of documents to support a normalized approach to back up your arguments.

 

[SkipVought]

The "dynamic" table look is an aggregation. Typically, when you perform a Pivot, it summarized detail data that is much more granular. If you are STORING data in this manner, or even transforming this view to a normalized table, you are loosing whatever granularity you might have had. That granulatity may or may not be important at some point.

All that I know from my experience, is that eventually, someone is going to ask a question, the answer to which will not be supported by the summarized data at hand.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[tractorvix]

Thanks all for the replies, I've had a good read of the link that Remou posted and agree that it should be a case of updating only 1 month of 1 years worth of data at a time, which I can easily acheive with the correct form layout.
I've previously always inherited databases which I've then had to manage including designing of new forms, queries and reports. This is the first time I've been given the opportunity to build one from scratch and would like to make sure that I get it right.
Thanks again for all your help, I'm sure I'll be bothering you all much more over the next few days / weeks!

Vicky