How to normalize and optimize this database?

[pheidlauf]

jdraw,

So far as I know, documentation is simply kept in versions of independent files (they make a change, and save it as a new version). There is no current common database for the information I'm working with. The "documentation" is simply found in independent reports about each job. This database I'm working on will hopefully be expanded to contain all relevant information so the forms and reports engineers independently produce can be automatically updated to the most recent data (instead of hand-typed into MS Word or MS PowerPoint).

Also, as for the data dictionaries: I'm prototyping the database in Access (a program I'm familiar with). When the design is finalized, I will be converting (at least the back end) into a MySQL database on a linux server. I don't know if there will be different features available for MySQL to help with version control/repositories/data dictionaries.

 

[jdraw]

I have limited knowledge of MySQL. I set it up once when I was dealing with the conversion of NAICS codes. There maybe some tools - I don't have any direct experience.
I have heard discussions of version control systems but they are dealing with vba code (programs) to the best of my knowledge.

Dictionaries are not trivial. They have to be designed and they have to fit into your data management processes. If you make them a separate task, they tend to become out of date and an after thought. Something, like sign off/approval, has to be in the process and the program/model is more of a byproduct of following the process.

Crystal Long (a very experienced user/developer and on forums from time to time) has a data dictionary/analyzer that you may find useful. At least it would be worth investigating.

Here is some info from a recent email she wrote to another group:
******************************************************
This first program I ever wrote in Access was a database analyzer. When I started using Access in 1993, I had already been developing
database applications for 10 years. I was surprised there was not a built-in way to generate a Data Dictionary so I wrote a tool to do it
and more.

Over the years, the Analyzer for Microsoft Access has been expanded by me and many others. As Bill Mosca says, Access developers are some of the last free spirits of coding ... and I'd like to believe that. I've learned so much from those who freely share, and I hope to pass it on. Sharing is in our collective best interest.

The 5 basic Analyzer reports are:
- Table Summary
- Deep Analysis (Data Dictionary + Value Analysis)
- Field List
- Table Indexes
- Relationships

I've been using this tool 20 years to quickly learn about database others set up. The name, Analyzer for Microsoft Access, came from the Object Analyzer, which was a feature built-in to the early versions of Access. Other have found this to be useful as well and have contributed code. Others, who don't even know they contributed, did ... thanks for posting! Over the years, the Analyzer grew.

Additional reports include:

- Lookup Fields
- Database Information
- Linked Databases
- Table Detail
- Field Statistics
- Object Summary
- Form Record Sources
- Form Control RowSources
- Font Styles
- LOOK in Form Control or Row Source (prompts for a parameter).
The Analyzer gets a LOT more data than is on any report ... What do you want to know about your database(s)? The Analyzer can probably tell you.

here is a 5-minute demo video about the Analyzer (just touches the tip of the iceberg):
http://www.youtube.com/watch?v=st3dcpVjwl0

the Analyzer for Microsoft Access source code is posted here:
http://analyzer.codeplex.com/

**********************************************************