The Best (Normalised) Format For This Table??

[dominicdunmow]

I have a user input form which is used by Salesmen to input details of contracts they have sold.

As a simple example the text boxes/comboboxes are:-

Start Date (year) - combo
Start Date (Quarter) - Combo
Q1 Revenue - textbox
Q2 Revenue - textbox
Q3 Revenue - textbox
Q5 Revenue - textbox
Q6 Revenue - textbox
Q7 Revenue - textbox
etc.

The salesman inputs the start year and quarter of his contract, then adds his projected quarterly revenue for the contract term.

My gut feel is to store the revenue values not under fields named Q1, Q2, Q3 etc. but as 2005 Q1, 2005 Q2, 2005 Q3 etc.

But this means finding a way of getting the input values to the correct fields in the revenue table and this is where I am stuck as the field the revenure is transferred to is dependent on the start dates.

Has anyone come across a similar scenario before? Or does anyone have any advice of how to work this??

Thanks

 

[lespaul]

You should read up a little on database design, The Fundamentals of Relational Database Design. You can search all the access forums for "Database Normalization" and read some of the posts that Willir has posted extensively detailing the thought process and logic needed to design databases. Once you have some ideas on your structure, come and post your proposed design and we'll critique it and maybe help you think about some things that you may have missed.



Leslie

 

[r937]

in case you don't like reading microsoft word documents (or waiting for them to download), there's another version of that exact same article as a web page here --

http://r937.com/relational.html

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[dominicdunmow]

I have The Fundamentals of Relational Database Design open and have been pouring over it for most of the afternoon, but while I think my logic is in line with the advice from the document, I guess it is how to transact it.

For example I can't use the usual method of indicating a "control" field for the form control to save the data in, because at the form level I don't know what the field title will be as it will be a calculation derived from other controls on the form.

For example if start date is 2005 Q1, then the control Q1 on the form should save it's value in a field derived from the start date + 1 quarter.

 

[lespaul]

Ok, then, let's think about it like this:

you have contracts, what kind of information do you collect on the contracts? name, startdate, enddate, the salesman that sold it, and quarterly revenue information. Anything else?


Leslie

 

[dominicdunmow]

There are other fields but I have them covered off - they're straight forward, its only the revenue per quarter based on start date that I can't work out.

The problem is the form has the fileds above, but the table will have fileds 2004 Q1, 2004 Q2 etc. So the difficlty is how to get the revenue figures from e.g. field Q1 in the form into field "calculation start date year/Q + 1 Quarter".

I'm unsure whther I'm explaining it correctly though.

 

[PHV]

but the table will have fields 2004 Q1, 2004 Q2 etc.
Seems you have to reread carefully this:

http://r937.com/relational.html

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[dominicdunmow]

Hmmn, I've read it today in trying to help in working this one out, but its provides generic relationship advice, I've used this to get the solution, I now have to work out how to get there and using which methods.

 

[SkipVought]

Something else to ponder:

Quartery Buckets are, many times, a reporting summary for a more granular date like Recording date, invoice date, receivable date. A date of this sort can always be grouped and summarized upward into Month, Quarter, Year. But if the data is stored by Month, Quarter or Year, is is impossible to decompose to in order to glean more granular information.

Skip,

_{[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?"}

 

[lespaul]

so each contract has many quarters:

Contracts
ContractID (PK)


ContractQuarters
ContractID (P-FK)
Quarter (PK)
Year (PK)
Revenue

ContractID        Quarter         Year          Revenue
   1                 1            2005          10000
   1                 2            2005           5000
   1                 3            2005           6000
   2                 1            2006           8000
   2                 2            2006          15000

Leslie