I work with a user who runs an analytical lab. For several years, he's kept track of the samples and data in an Excel spreadsheet. Last year, he decided that with nearly 7000 samples, a database would really offer more flexibility. In the first pass of moving the data to Access, we ended up with a glorified spreadsheet! Now with some more experience under our belts, we want to completely redesign the database by creating additional tables, and deleting or renaming most of the fields in the main table.
My first question (I'll be posting each individually to spread out the points) is for some advice on the design. For each sample, there is information about who submitted it, how and where it was collected, and then the actual results from the analyses. I have created and populated 3 new tables. One contains an index number and information on the "client". The second contains another index number and information about the specific "researcher". The third has an index number and information about the researcher's "project". The index numbers and client-researcher-project names are unique within each table, and each sample is identified by some combination of these three index numbers, plus a unique sample number.
What remains is the sample information. There is some descriptive information about each sample that is provided by the researcher, then the results. There are potentially about 40 analytical tests that can be run. My question (finally) is whether I should divide the sample information into two tables: Descriptive and Results. They would be linked by the sample number. Or is it more efficient to leave all the sample information in a single table?
Any other design information would be very welcome. I'll save my "how-to" questions for later questions.
Start Free Trial
