I'm in the early stages of designing a database for an organization that does environmental remediation and research. (Small org, so I'm basically the data person.) One of the major types of data we have is the results of tests for a range of soil contaminants, so in my schema draft, the sampling points within work sites are linked to tables of associated measurements. (Currently there's one table per sampling point but I'm open to other designs.)
The problem is that which contaminants are tested for mostly can't be known in advance for future projects and can change within a project if new funding appears or questions arise. There are hundreds if not more of substances that could reasonably be tested for (although ~20-30 are core), so listing all possibilities doesn't seem practical. And the contaminants data is key to the organization's work and is probably the thing that will be queried the most. How would you handle a situation like this?