Database Design

[Weste]

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]

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.

 

[Weste]

John,

Thanks for your response. If we use the staging tables to bind the data
entry form and then delete the data from this table after it is entered how
would edits to data be handled? Would you take the data from the normalized
table and place it in the staging tables for editing? Isn't this approach
causing more overhead and processing time - ie the data will constantly be
going from a normalized to de-normalized state and visa versa?

In addition, the client wants the total budget dollars to be shown on the
data entry screen. Not sure how that would be possible since the rates are
based on job role, fiscal year, and month and the form is bound to a
de-normalized table. A join can no longer be made between the budget table
and the rate table to obtain the correct labor rate. I guess we could write
some code to extract the monthly rates and store them in variables but again,
isn't this adding alot of overhead and processing time to the application?

Thanks.

 

[John Nurick]

All these are valid points and may tip the balance in favour of using an
unbound form. But I feel it would be very difficult and costly to
program an unbound form to behave like a continuous form bound to the
client's unnormalised LabourBudget table.

Probably you need to get a firmer understanding (better, agreement) on
what is needed in the way of data entry and editing facilities. Is it
really necessary to see multiple LabourBudget (in the client's
definition) records at the same time? If not, then an unbound form is
probably simpler than the staging table approach.

I don't think that the requirement to display total budget dollars poses
much of a problem. You'd normally generate this from a query on the
"real" tables, and issue is simply that if you're appending or editing
records in the staging table, the "real" tables won't be up to date. To
overcome this, you could use the AfterUpdate event of the bound form (or
of individual controls on it) to make the appropriate changes to the
"real" tables.

 

[Weste]

Thanks John - you were a great help!

All these are valid points and may tip the balance in favour of using an
unbound form. But I feel it would be very difficult and costly to
program an unbound form to behave like a continuous form bound to the
client's unnormalised LabourBudget table.

Probably you need to get a firmer understanding (better, agreement) on
what is needed in the way of data entry and editing facilities. Is it
really necessary to see multiple LabourBudget (in the client's
definition) records at the same time? If not, then an unbound form is
probably simpler than the staging table approach.

I don't think that the requirement to display total budget dollars poses
much of a problem. You'd normally generate this from a query on the
"real" tables, and issue is simply that if you're appending or editing
records in the staging table, the "real" tables won't be up to date. To
overcome this, you could use the AfterUpdate event of the bound form (or
of individual controls on it) to make the appropriate changes to the
"real" tables.