Hi there, its been a while since I last posted.
I am working on a new application that basically allows the users of the application to enter projected prices for various products over a series of time.
The company's traders negotiate deals with customers as to what we can see the products for. The company's buyers then know what margins they have to work with.
There is already a VB6 application with access BE in place. Although it works reasonably well, it is let down by poor database design. The existing developer had obviously never heard of normalisation and as such has used one underlying table with fields for product name, product description but also for the months i.e. y1m1a, y1m1b, y1m2a.....
In total there are 104 fields in the table!!
It as if he has used the table more as a spreadsheet. He is then loading the whole table into a dataset in the app.
I have been asked to rewrite the application long term as either a Windows Form or Web Based format (vb.net/asp.net).
Now I am just looking for some advice as to how I would a) structure the db and then b) expose this to the application.
a) There should be a table for product (that is farely obvious). The month names i.e. Jan 07, Feb 07... should not be fields in any table as they are variable. But thats where I get a little stuck. A product can have many prices over a series of time, and each price has a month for which it applies. But how could I structure this? Prices do need to be stored and the months must be able to go from now to infinity (with the ability to look back over the years and product trend charts etc)
b) Once the database structure is complete I then need to decide a way to use this in my application. Although I could use a gridView control in either VS 2005 or Asp.net, I am still limited by the fact that these would have headers for all of the fields. However I need my months along the top of the control and products as row headings (almost like a crosstab query, or a spreadsheet).
Any advice would be appreciated. The main thing I need to keep in mind is that the application must store all prices (past present future), the months to be updated dynamically and products to be entered easily.
Thanks,
Ben
I am working on a new application that basically allows the users of the application to enter projected prices for various products over a series of time.
The company's traders negotiate deals with customers as to what we can see the products for. The company's buyers then know what margins they have to work with.
There is already a VB6 application with access BE in place. Although it works reasonably well, it is let down by poor database design. The existing developer had obviously never heard of normalisation and as such has used one underlying table with fields for product name, product description but also for the months i.e. y1m1a, y1m1b, y1m2a.....
In total there are 104 fields in the table!!
It as if he has used the table more as a spreadsheet. He is then loading the whole table into a dataset in the app.
I have been asked to rewrite the application long term as either a Windows Form or Web Based format (vb.net/asp.net).
Now I am just looking for some advice as to how I would a) structure the db and then b) expose this to the application.
a) There should be a table for product (that is farely obvious). The month names i.e. Jan 07, Feb 07... should not be fields in any table as they are variable. But thats where I get a little stuck. A product can have many prices over a series of time, and each price has a month for which it applies. But how could I structure this? Prices do need to be stored and the months must be able to go from now to infinity (with the ability to look back over the years and product trend charts etc)
b) Once the database structure is complete I then need to decide a way to use this in my application. Although I could use a gridView control in either VS 2005 or Asp.net, I am still limited by the fact that these would have headers for all of the fields. However I need my months along the top of the control and products as row headings (almost like a crosstab query, or a spreadsheet).
Any advice would be appreciated. The main thing I need to keep in mind is that the application must store all prices (past present future), the months to be updated dynamically and products to be entered easily.
Thanks,
Ben
