oumahexi said
You especially need to be careful NOT to take things for granted. Often users are so familiar with what they do, that they forget to tell you some basic things, which will most probably cause issues later (Why doesnt it do that? We thought you knew that .... sort of stuff). The better understanding you have, the less likely this is to happen. Its not the 95% of usual stuff that is hard to manage/design - its the 5% of special cases and exceptions.
To some extent the need for all this depends on who is paying for the work, (and the overall complextity) - ie your relationship with the client - but the design phase is often formally signed off with a customer acceptance of some sort - and can be pretty time consuming. It also gives a reasonable breakpoint, as you may be able to cost this, and decide whether to go ahead at this point.
Quoted for truth.
I have designed an extensive water quality database for the native american organisation where I work. This database is setup based on the concept of water quality sampling 'runs', and not only stores the information used by the Water Resources department, but must export data according to the schema required by the EPA's 'STORET' database.
My experience in designing databases for folks in the natural resources field has been almost a textbook illustration of Gemma's point. When scoping out the project you will find that people will gloss over the small details that will make or break your data model (which is represented by your table structure).
For example, they may say that they collect a range of metrics at each site but neglect to mention that, if the water at the site is stratified, they may take more than one measurement of some of the metrics at various depths related to the position of the halocline. Or they may discuss recording totalizer readings from wells, and neglect to mention that on some wells there can be more than one meter.
What you need to be aware of is that they think they are helping you by simplifying things. And in some cases, they will decide internally that it's a detail that you don't need to know and flat-out lie to you. (And come back later when they realize that some of their data doesn't 'fit' into your data model, or they need to split out data based on this missing piece of information.)
If you are doing this on a contract basis, you will need to make sure that they understand that if they gloss over details during the scoping phase that the final product will not necessarily handle data resulting from those exceptions. Really emphasize that you need to know about the rare events and departures from the 'normal' protocol in order to do your job.
And cross examine them. Repeatedly. They will still lie like dogs and you need to catch them in the little details.
Ask if they have a Quality Assurance/Quality Control Plan (QAPP) for their program. A good QAPP document will provide a very helpful guideline to their activities that will help you construct your data model. But be aware that there will undoubtedly be exceptions to what is in the QAPP that will crop up.
Personally, I feel that unless you are extremely well acquainted with the sampling program, or have ongoing access to the field personnel to ask questions, then you're really facing an uphill struggle even if you were an experienced Access developer.
Other things to look out for:
Historical data. WQ program objectives and protocols change over time. Many times these changes are never documented. If your database is intended to hold historical data, in addition to data being collected under the current protocols, then you will be facing further challenges trying to deal with that.
Different items of equipment may be read using different units (ft versus meters, ft and inches versus decimal feet, gallons versus acre-feet, etc).
Equipment calibrations, and calibration checks are a major PITA. There is a distinction between these types of events. Essentially, calibrations involve adjusting an instrument, or having the instrument self-adjust, to obtain a certain value when measuring a known reference solution. A calibration check involves checking the instrument reading (without adjustment) while measuring a known reference solution.
Calibrations and calibration checks may be done prior to a run, during a run while conducting a site visit, and followoing a run.
Sampling runs typically involve visiting a predictable suite of sites to collect samples in a day. But make sure to allow flexibility in the number of sites visited. It is not unusual to have additional sites get added temporarily following events like sewage spills, flooding, oil spills, etc. And weather and other factors can reduce the number of sites visited on any given day.
Make sure that they understand that the data entry burden will be considerable: although you can automate a lot of it if their sampling program has predictable patterns. But automating to save data on entry burden can be very complex and, on occasions where exceptions to the pattern occur this will mean the user will have the responsibility of either entering everything manually, or else correcting the results of the automation process.
Water quality people are extremely concerned about quality control. You will need to provide them a way to check their data entry (I use a report that they print off and check against their field forms). You will also need the ability to indicate that certain data records may be less relaible than others.
Get a hold of their field forms, preferably some blank and some filled out. If they use field notebooks, then you may need to help them come up with field forms to organize the data in a predictable way for the person doing data entry. If they are unwilling to change their protocol to use field forms then I would suggest declining the contract.
For a database to work there needs to be real world rules for it to rely on. Field forms keep field personnel on the straight and narrow. Notebooks are an invitation to a free-form ever changable sampling approach. Do yourself a favor and avoid touching a sampling program that refuses to commit to a structured approach. Your reputation is more important in the long term than the money you might make in the short term, and an unsuccessful product will be held against you even if the reason for failure is entirely a consequence of the wq program constantly changing the rules of the road, and not laying out their needs clearly and comprehensively.
My final point is that you're in for an extremely steep learning curve if you rate your access ability as a 2 out of 10. This project consumed at least 5 months of my time, with 3 months of initial scoping and construction, and another 2 months on-and-off over the next couple of years making modifications and additions. There are still more changes and additions awaiting my attention now that the department is realizing just how powerful and useful the database is for them.
I would rank myself about an 8 out of 10 on the same scale (I am nowhere near the league of some of the other AWF VIP's like Pat, Bob, Banana, RuralGuy and many others who all rate a 10 from me). I have a good feel for table normalization and data models, I am very familiar with making forms, I can get by with SQL, and I can usually get VBA to do what I want even if my coding has little elegance. You're going to need to develop that kind of knowledge too in order to make a product that functions well and has a reasonably user-friendly interface.
The good news is that pretty much everything I know, I learned right here on this forum and by trial-and-error on other projects, and there's no reason you can't too. The bad news is that it took me about 2 years from starting out to reach the level I'm at now. And I still have a lot of learning to go...so be warned, you're embarking on a big project.
I would be willing to share the table design I used, and perhaps an example of the field forms we designed to help you starting out. But you should be aware that the design I have may not be suitable in your situation if their real world protocols differ significantly. You will still need a good understanding of normalisation to understand why the tables are structured the way they are (and I am quite sure that my design is far from perfect). But the database itself is the property of my employer so I cannot share that with you.
. You mention that you would be willing to share the table design you used, and perhaps an example of the field forms we designed to help me starting out. I have NO idea where to start, so maybe that would be a good plan? - Thanks .
