Conceptual design advice for relational database

[Guest]

First, my apologies to this lengthy email, as it seems these news groups are
more focused on short-term problems and perhaps my email is inappropriate for
this newsgroup, but I’m going to try anyway I think what I’m mainly
looking for is either validation I’m on the right track or recommendations on
how to improve.

I have created a relational database for the purpose of formulating our
military command’s budget requirements over a course of 7 years and also to
track execution of funds during the current operating year.

So my design consists of the main related table which holds the index field,
detailed requirement description, and the 7 separate fields for 7 years (1
current year +6 programming years).

Related tables include information for:
office/command responsible for the requirement
funding account information
mission function the requirement supports
requirement classification type (ie, travel, supplies, equipment, contracts)
and audit trail information for funding changes during the year of execution.

I have then created a BASEquery that merges all this information and I use
this BASEquery as a source for all my other subqueryies/reports/ and forms.

My endstate objective is to publish the DB for other users, so they can
enter their requirements, but I’m not there yet, because, well, I just don’t
know how to do it yet.

Problems: many, but my two biggest conceptual issues are as follows:

1) I exported data from the BASEquery into a spreadsheet in order to send
out files to each command to make changes to their requirements. (I know this
isn’t what should be done, but these people are non-access users and I
haven’t taught myself the whole frontend/backend concept yet --- getting
there though). I thought I could then take their changes and reimport the
spreadsheet files. This didn’t work out so easily because during the export,
the information in the lookup fields converted to text. So then when I tried
to append, I had to change the text fields back to their numerical code. Now
I’ve been advised I should use combo boxes and list boxes on my forms which I
haven’t done..I just use the lookup field from the baseQry, but will go back
and study this.

2) My next problem is figuring out the best way to consider tracking
requirement changes during the execution year. We can’t just go in and
change the figure, but must show the +/- change so the database needs to
support an audit tracking function. What I have set up is a form where the
analyst can go in and apply the decrements or increases through out the year
and then the database re-totals the individual requirement. This works fine
until the end of the year when I need to roll the end of year execution
totals over to be the next years start point. To get around this, what I did
was created a FY06Backup to the main table, cleared all the records from the
main table, then appended the main table with records from the backup using a
query that totaled the requirements. Can’t I just create these steps into a
macro to run every year?

Thanks

 

[Roger Carlson]

Hi Laura,

I'm not going to try to answer your specific questions, because your basic
problem lies in this sentence:

So my design consists of the main related table which holds the index field,
detailed requirement description, and the 7 separate fields for 7 years (1
current year +6 programming years).

You should NOT have 7 separate fields, one for each year. This is what we
call "committing spreadsheet". It is a fine design for a spreadsheet, but
absolutely wrong for a relational database. Instead, you should have a
separate RECORD for each year's data. So instead of this:

ID......ReqDesc....2006.......2007......2008
============================
1.........Req1..........3.00........4.00.......2.00
2.........Req2..........6.00........1.00.......4.00

You'd have:

ID......ReqDesc......Year.......Amount
==========================
1........Req1............2006.......3.00
2........Req1............2007.......4.00
3........Req1............2008.......2.00
4........Req2............2006.......6.00
5........Req2............2007.......1.00
6........Req2............2008.......4.00

Now this is just an example. Please don't view it as definitive. But the
point is that instead of storing information (year) in the column header
(field name) you store the value itself in a field. This will allow you to
add and subtract amounts by adding additional positive or negative values in
a new record.

7........Req2............2008.......-2.00

This will also make it possible to aggregate data from one year to another.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Guest]

Roger,

Thanks for your post.
When I set up the table, I thought about how to address the year problem
because I knew I'd have a problem with having to change each and every
query/report/form etc when I moved from one year to the next. So what I did
in the main table was set up the design like this:

ID
ReqDesc
CurrYr
PgrmYr1
PgrmYr2
PgrmYr3 and so on....then in my reports or forms, I just renamed the labels.

I thought by having the year in every record I would be committing the error
of having repetitive data, (sorry, not up to speed on all my db lingo) which
I thought was to be avoided.

I gotta think through what you say. You don't have to tell me how, but is
there a way to automatically fill in the date field to reflect the current
year, but still allow the user to manually change, if they need to enter a
different year. I wouldn't want a separate relational table to store only
the years, would I?

I certainly see how at the end of FY06, I could run a query that sums all
the FYo6 entries, and then append those sums back into the table and change
the year to 07 to be the new years start position. hhhmmmmm.

Next question, excel let's you transpose records...does access have an easy
way to fix this or must I go in and do a lot of handjamming?

 

[BruceM]

It would help if you could describe the real-world situation you are trying
to manage. If you have a retail business you may have said that you have a
list of customers, each of whom places orders over and over, each of which
may contain many items. In your situation, what is being repeated, and what
stays the same? Entering a year repetitively is not necessarily a problem.
In the customer situation I described you probably hope that each customer
places several orders each year. You can filter the records for a single
year if needed. Storing the customer information in every order, on the
other hand, could be a problem if for no other reason than that a
typographical error could cause a customer's order to be lost.

An Access table should contain information about a single real-world entity.
I gather that there is a requirement for which you need a description, but
after that I don't follow you. Some examples, modified and obfuscated as
needed, would help.

For adding the current year, the default value for a field can be set to
=Date(). The default value is only in effect for a new record.

 

[Roger Carlson]

Getting rid of repetitive data is a good thing. It's what normalization is
all about. However, the solution is NOT to do as you have done. That
violates the first rule of normalization. You may well need other tables.
I can't say for sure and I don't like recommending table designs in a forum
like this because most business processes are far more complicated than
people communicate in posts.

Get a copy of "Database Design for Mere Mortals" by Michael Hernandez.
After reading that, go to my website and download the database design
tutorials. http://www.rogersaccesslibrary.com/TutorialsDesign.html. These
tutorials use Hernandez's process. That will give you a good idea of how
you should design your tables.

Access does not have a transpose feature like Excel. But one of the samples
on my website (NormalizeDenormalize2k.mdb) shows how to write denormalized
data into a normalized format.


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Jamie Collins]

You should NOT have 7 separate fields, one for each year.
Instead, you should have a
separate RECORD for each year's data. So instead of this:

ID......ReqDesc....2006.......2007......2008
============================
1.........Req1..........3.00........4.00.......2.00
2.........Req2..........6.00........1.00.......4.00

You'd have:

ID......ReqDesc......Year.......Amount
==========================
1........Req1............2006.......3.00
2........Req1............2007.......4.00
3........Req1............2008.......2.00
4........Req2............2006.......6.00
5........Req2............2007.......1.00
6........Req2............2008.......4.00

What if a value is required for each year? For example, say the
following is illegal:

ID......ReqDesc....2006.......2007......2008
============================
1.........Req1..........3.00........NULL.......2.00

With your proposed structure, where each year's 'amount' is in a
different row, how would you enforce the rule?

Jamie.

--

 

[Roger Carlson]

Not sure I understand. You can always set the Required property on the
field. That would not allow a NULL in the field.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Jamie Collins]

Not sure I understand. You can always set the Required property on the
field. That would not allow a NULL in the field.

Sure, if you (as the OP does) have three columns for 2006, 2007 and
2008 respectively, then you could declare them all as NOT NULL.

However, you are proposing that this design is 'wrong' and should be
replaced by three rows, one each for 2006, 2007 and 2008. With your
proposed design, how would you enforce the same business rule i.e. that
there must be rows for 2006 and 2007 and 2008 (or none at all)?

Jamie.

--