Re: Database Design

Hi Weste,

You are certainly right to demand a properly normalised data structure.

But as you know crosstab queries aren't updatable so can't be used
behind a data entry form. And it's a lot of work to code an unbound form
that works like a continuous form, especially if it also has to
denormalise the data.

So if a continuous form is the best design for data entry, I wouldn't
hesitate to use a denormalised "staging" table bound to the data entry
form to hold the data as it's entered. Once the entry is confirmed, I'd
use it to create or update the corresponding records in the "real"
normalised tables, and delete the record from the staging table.


On Mon, 5 Mar 2007 16:55:08 -0800, Weste
<Weste@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

I am designing a database for a client to track budget data. The labor data
is entered by FTEs (Full Time Equivalent Employees) and the dollars are
calculated by multiplying the appropriate labor rate by FTEs. The labor rate
is based on job role, fiscal year, and month. The client also wants to the
ability for 12-month rolling budgets. I recommended the following table
structures.

Table: LaborBudget
? LaborBudgetID
? ProjectID
? JobRoleID
? ?.

Table: LaborBudgetDetails
? LaborBudgetDetailID
? LaborBudgetID
? FiscalYearID
? FTEs
? ?..

Table: FiscalYear
? FiscalYearID
? FiscalYear
? FiscalMonthNumber
? ?

Table: LaborRates
? LaborRateID
? JobRoleID
? LaborRate
? ?.

The client has just completed 2 Access classes and doesn?t agree with my
design.
They want the table design as shown below because it will make coding of the
data entry screen easier.

Table: LaborBudget
? LaborBudgetID
? ProjectID
? JobRoleID
? FiscaYear
? Jan
? Feb
? Mar
? Apr
? May
? Jun
? Jul
? Aug
? Sep
? Oct
? Nov
? Dec

They want the data entry screen to look like the design below.

Project Job Role Jan Feb Mar Apr May Jun Jul Aug Sep
Oct Nov Dec

I explained that this is not a normalized database design and will cause
more problems down the road when building queries, reports, etc. Their
response was ? then will have 2 sets of tables ? 1 for data entry (the
non-normalized design) and then copy the data to the normalized tables for
reporting. They don?t want the normalized design because a crosstab or
unbound form will need to be used for the data entry screen.

Can anyone provide anything that I can use to help convince them how to
correctly design a database? Thanks for your help.

Weste

--
John Nurick [Microsoft Access MVP]

Please respond in the newsgroup and not by email.
.

Relevant Pages

  • Re: Database Design
    ... program an unbound form to behave like a continuous form bound to the ... what is needed in the way of data entry and editing facilities. ... So if a continuous form is the best design for data entry, ... calculated by multiplying the appropriate labor rate by FTEs. ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Database Design
    ... "John Nurick" wrote: ... program an unbound form to behave like a continuous form bound to the ... what is needed in the way of data entry and editing facilities. ... So if a continuous form is the best design for data entry, ...
    (microsoft.public.access.tablesdbdesign)
  • =?Utf-8?Q?Re:_Canceling_and_deleting_a_rec?= =?Utf-8?Q?ord_via_code_=E2=80=93_help_please.?=
    ... As I mentioned in my original post the data entry has already been started ... the earliest harvest date I open an error form and display an error message. ... properties (lower pane of table design), ... and if you cancel the event the record doesn't get saved. ...
    (microsoft.public.access.modulesdaovba)
  • RE: Record Form
    ... If you want to check the form style, open the form in design view and click ... on the format tab and check the 'default view'. ... Change the 'Data Entry' property to 'no'. ... Verify/change the 'Navigation Buttons' property to yes. ...
    (microsoft.public.access.gettingstarted)
  • Re: Table design
    ... This is the data entry aspect of what we are looking for. ... the table design is first. ... >>> to pay ahead for 'Vacation' pay. ... and flagging each timesheet entry as paid. ...
    (microsoft.public.access.tablesdbdesign)
Loading