Live measurement result database vs. upgrades/changes

[GioVleMat]

Hello,

For our QC division, we currently have multiple operators in different locations performing measurements. At this moment, the results are stored in separate Excel "databases", which is a hard to maintain situation. As improvement, we would like to use an Access database that receives its input from different macro-enabled Excel input forms (this way our operators will not need to change their way of working + we can keep all of the logic that is in the macro-code). The database will then be available at a shared location and the operators can use their local copy of the input form.

Before we start setting up the Access database, we were wondering how it would be possible after the release, to allow for upgrades/changes to the database. Imagine we would like to add several additional fields to the database. At this moment I can think of no better solution than to use an offline copy of the database to add these fields, test is and then schedule an upgrade in which we either duplicate all the changes on the live database or install the updates database and merge all the records from the previous live database version.

This does not seem very elegant, but I have not found anything that might point us in the right direction. The key elements for us are to keep the downtime as low as possible and to avoid (as much as possible) migration of large datasets from one version to the other. Is there a more "professional" way of handling this? If so, can we take this into account in the database design?



Kind regards and thank you for any suggestions.

[June7]

Split the database into frontend and backend files. Backend has data. Frontend is user interface and links to backend tables. Make changes to a development master of the frontend and distribute copies after modification. Some changes to backend will require 'down time' but will not have to worry about merging data between 'versions'. This sort of design modification to backend should be a very rare occurrence in a fully deployed database.

[Micron]

Imagine we would like to add several additional fields to the database.

What's easy to imagine is that you're approaching the design using your Excel brain. If you envision having to regularly add table fields, you must be designing it all wrong. As much as splitting is recommended, I don't see how that is a solution for dealing with what looks to me like it will be a flawed design. Maybe you need to research entity/relationships and normalization for databases and see if I'm close or out to lunch. Whatever you plan to add as fields just might belong in rows.