Structuring a new database?

[RipperRoo]

Hello! Guess who's back!


and i intend spending a bit of time here again now i have an excuse to use the web once more!

my problem is this, i have a little coding experience but I’m brand new to programming & databases and need a little help, this is a sort of volentary project for work, and if you dont know what i do for a living, and cant work who im employed by from this post you need to get out more






I’m developing an application in Delphi 7 that needs to be able to record one weeks worth of sales figures for around 30 to 40 distributors who sell just 1 product, but because of the nature of that product its sold to the distributors at 4 separate prices, the sales force is split into four zones and i need to record the number bought at each price by each retailer six days a week.

the system is going to be set up till style with sales being input and totals for each transaction output, but tbh that’s just background, where im really stuck is how to lay out the database tables and the relationships between them, i just cant get my head round it!

The info i need to use daily is as follows:
Date & Day Monday/Tuesday ect.
Distributor ID number,
Distributor name (as a double check the id number is correct)
Amount sold at price 1 for each Distributor
Amount sold at price 2 for each Distributor
Amount sold at price 3 for each Distributor
Amount sold at price 4 for each Distributor
Total Sold This Week for each Distributor
Total Sold at Price 1 & 3 for each Distributor (sales bonuses are based on these two price bands)

Totals for all 4 price bands for all Distributors

and i also need a way of recording current stock levels,
the question is how do i structure it?

Do I need a separate table for each day and another for totals?
How should I lay out the relationships?

As far as reports go i will need access to the totals of each Distributor for the week to date, a view of the totals to date for all Distributors on one page would also be nice and a print out or save to excel/text/word file of the whole lot at the end of every week when the database would hopefully reset itself and remove previous figures (but only if saved to backup)

I know this is a lot of advice to ask for in one post, but I’m writing this app as voluntary work and I think I have bitten off a little more than I can chew but would hate to let anyone down.








Taking all this info into consideration, how would you proceed with the database?

thanks in advance for any help you can give, and if anyone is interested i'll be sourceforging the project next week

[RipperRoo]

and if anyone is real interested here is the current compiled exe, but if i were you i would close any running programs and save anything your working on before running it, as this is 'in progress software' and hasnt been fully tested yet.

it may only be small (it unzips to around 9mb right now) but run at your own risk.

as an extra bonus ill try and squeeze the delphi 7 source code in as well

the source is split into 2 parts, unzip each part, combine them in the same folder and unrar.

if you want a look at it with the IDE (Intergrated Developer Environment) im using its available free and legal at http://www.liteapplications.com/

and another tip, because the software is set to kick out a cash draw using epson recipt printer specific control codes its not advised you click OK on the totals window as i dont know what efect this may have on a normal printer.