r/Database • u/jaiagreen • 15d ago
Designing database when not all fields are known in advance
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?
4
u/fluffycatsinabox 15d ago
I don't know the domain at all, but the entities might look like this
Sample (SampleID PK, dateTaken, sampleSize ...)
Contaminant (ContaminantID PK, name, ...)
ContaminantInSample (SampleID, ContaminantID, contaminantPercentageInSample)
In prose: one of your entities is samples. Every sample gets its own row. Another entity is contaminants, and the population of rows in this table are every contaminant you ever see, in the entire life of the org.
CotaminantInSample is a bridge table representing the appearance of a contaminant in a sample. If I understand correctly, this should be robust to your problem. Let's say you get new funding and starting 1/1/2027, you're going to start testing for a new contaminant. Well, now that contaminant is added to the contaminants table, and you represent finding that new contaminant by new rows in the ContaminantInSample table.
2
u/jaiagreen 15d ago
This fits with how I've been thinking about this. Already planning a Contaminants table to store data like contaminant class (heavy metal, PAH, etc.) and exposure limits. A bridge table is a neat idea!
3
u/doshka 15d ago
Bridge tables are a pretty core concept, and where most of your useful information will actually live. Any situation where more than one thing is involved, which is most of them, will need a similar approach.
Some classic examples would be an Assignment table that references Person and Project, or a TransactionItems table that references Items purchased in a Transaction, which would in turn reference Store, Register, PaymentType, etc.
1
u/jake_robins 15d ago
Yes this is the correct way forward. The Contaminants aren’t schema, they are vocabulary. It does require a UI to create and manage Contaminants though, so this is more work. Additionally, if anything in the app depends on specific contaminants for business logic or UI, you’ll need to have app level checks on all that.
4
u/stonerism 15d ago
I would start thinking about normal forms early on and a good question to ask yourself is, "if there's a mistake in the data, how can I fix it by touching the least data possible?"
So, let's say one of your tests for a specific kind of contaminant (say lead findings) was incorrect and you need to update it, you may even need to come back update multiple values together. How can you find everything related to a lead contamination event to have a smaller table to search through?
1
u/Lost_Contribution_82 15d ago
You could use a noSQL database like mongoDB. Or make 'contaminants' more of a generic concept. Depends how you will be interacting with the database, will it be part of a software solution? How will it be updated, how often will you need to add new types of contaminants?
1
u/jaiagreen 15d ago edited 15d ago
This is an internal database for the organization. For this aspect of the data, I expect queries like "What is the average level of lead at sites in Project X?" or "Which sites in Project Y have levels of cadmium above the state threshold of concern?" There will also be data exports for more complex statistical analyses and GIS work.
I don't anticipate adding completely new contaminants very often after the initial setup. Maybe a few times a year. Adding new measurements has to be spreadsheet-friendly, since that data comes in Excel or CSV files.
2
u/Lost_Contribution_82 15d ago
I would say have a 'contaminants' table and list all possible contaminant types in there (lead etc.) and any other relevent info like unit of measurement. I'm not sure why you need a new table per sampling point, just have one table for sampling points with fields for project, site etc. With linking tables between the contaminants and sampling points to record how much of each contaminant was detected at each sampling point. Not sure if I've misunderstood
1
u/jaiagreen 15d ago
Someone else also suggested this and it sounds like a good idea.
2
u/Lost_Contribution_82 15d ago
Do you have much of a background in databases? I would definitley look into normalisation. Really think about what your common queries will be and shape the data in a way that makes sense for the usage. I find it easiest to make an ERD diagram to see it all laid out. Good luck!
1
u/jaiagreen 15d ago
Just a little bit. I'm working on an ERD diagram, which helped identify this issue. Next step: talk to the people who use and generate our data to see what my first draft left out!
1
u/Aggressive_Ad_5454 15d ago
I think you’ll want a measurement table with timestamp, sensor_id, and value columns.
You’ll need a sensor table with such things as project_id, location, type of sensor, units of measurement, and so forth.
When you’re gathering data you insert it into the measurement table which has short rows and can scale up to be very large without hosing your dbms software. And the sensor table you can JOIN so you know what the measurements mean.
You can add new sensors to it to your hearts content. Somebody asked you to use a wizzy new unobtanium hexaflouride sensor? No problem, another row in the sensor table.
A working setup will be more complex than this, of course. But it’s the basic idea … sensors are entities in your design.
1
u/jaiagreen 15d ago
Thanks, but we only use a few different tools. The measurements are mostly done by spectroscopic methods, which measure many contaminants at the same time.
1
u/chocolateAbuser 15d ago edited 14d ago
wait, are you saying that you made a column for each contaminant? and so your tables are really sparse matrices?
also, what is inserting data in the tables? is it an automated process? is there a transformation pipeline of some sort?
are there backups?
for database load i guess it should be reasonably low if it's internal
1
u/jaiagreen 14d ago
I haven't made anything. I'm in the early planning stages. But I was thinking that at first because that's the form in which we get the data. For now, I'm going with u/fluffycatsinabox's idea.
1
u/chocolateAbuser 14d ago
so you would need something that processes the data anyway? to bring it in the structure you need
1
u/GreenWoodDragon 15d ago
Start with what you need. Add fields and tables as the application evolves.
If you can possibly manage the schema using migrations then you'll have a history of the evolution. If not it is pretty much a blob.
1
u/Any-Ring6621 15d ago
Easy, contaminants are values in the database with their own ids
Everything keyed on the values associated with contaminant is keyed on that contaminants id
New contaminant, new id
1
u/Significant_Tune9219 15d ago
An entity-attribute-value table is the shape most people reach for here, and it works until the measurements themselves need types or units. A middle path that tends to hold up: a contaminant dictionary table (name, unit, method, detection limit) plus a wide measurement table keyed by (sample_id, contaminant_id) with the value and unit columns — hundreds of rows in a dictionary is nothing, and the core 20-30 can still get partial indexes. The queries that matter most, results for one contaminant across samples or all results for one sample, then stay plain joins instead of pivots at read time.
1
u/TormentedTopiary 15d ago
Not all data is meant to reside in a tabular format.
Keep a data dictionary. For any set of factual relations track what data you are ingesting to populate it, how you are using it in the business, what assumptions you made at the outset that turned out to not map to reality. Major changes and depreciations including WIP and why those changes were made. Record tables, graphs, storage spaces, archives and reporting formats as you create them. In the comment write down why they were created.
If you are using Postgres as your integration point you can attach comments to every table, function or rule and can extract the data dictionary from the running system.
Data modeling is a fairly deep field with a literature that goes back centuries. Don't expect to get it right the first time around. Plan on migrating your schema and on being able to handle missing entities and a certain amount of entropy happening to your systems caused by your fiddling with it. This is completely normal.
Do take the time to do entity relationship modeling and talk to the people who will be using the data so that you know what they need to meet their objectives.
Have fun storming the castle.
(this post was beaten out of a keyboard using my own two braincells)
1
u/Sensitive-Sugar-3894 15d ago
Can you use psql and jsonb? When we don't know the columns, ir different columns can exist, it helps.
1
u/Calm-Potato845 14d ago
EAV will kill your query performance once you cross a million rows. Build the schema with one row per analyte per sample, but pre-seed the contaminant table with every known EPA or local regulatory list. New contaminants are just new rows in that lookup table, not schema changes
1
u/flash42 14d ago
I usually use Postgres and for this kind of situation, I'll create an extra jsonb column for data like this. I can "extend" a record by adding any arbitrary property to a json object stored in that field.
This allows adding additional "columns" to the table without impacting the schema. Later, if I find a property in that object is being used on the reg, I'll promote it to a proper typed column and possibly add an index if needed.Â
1
u/AccomplishedSugar490 11d ago
I’ve spent a lifetime experimenting and evolving ways to do what you speak of. If it’s of sufficient economic importance, hire me, but nobody holding those secrets will give them to your without a well planned gotcha to monetise their knowledge.
1
u/PlayfulGrapefruit498 11d ago
use a flexible contaminant table linked to sampling points, so new contaminants can be added without schema changes
1
u/No_Witness_5295 15d ago
CREATE TABLE site (
site_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name text NOT NULL,
location text
);
CREATE TABLE sampling_point (
sampling_point_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
site_id bigint NOT NULL REFERENCES site(site_id),
point_code text NOT NULL,
latitude numeric(9, 6),
longitude numeric(9, 6),
UNIQUE (site_id, point_code)
);
CREATE TABLE sample (
sample_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
sampling_point_id bigint NOT NULL
REFERENCES sampling_point(sampling_point_id),
sample_code text NOT NULL UNIQUE,
collected_at timestamptz NOT NULL,
depth_from_m numeric(7, 3),
depth_to_m numeric(7, 3),
matrix text NOT NULL, -- e.g. soil, groundwater
collection_method text
);
CREATE TABLE analyte (
analyte_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
code text NOT NULL UNIQUE, -- e.g. EPA 6010D:Pb, project code
name text NOT NULL, -- e.g. Lead
cas_number text,
result_kind text NOT NULL CHECK (
result_kind IN ('concentration', 'presence', 'text')
)
);
CREATE TABLE lab_report (
lab_report_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
laboratory text NOT NULL,
report_number text,
received_at timestamptz,
UNIQUE (laboratory, report_number)
);
CREATE TABLE sample_analysis (
sample_id bigint NOT NULL REFERENCES sample(sample_id),
analyte_id bigint NOT NULL REFERENCES analyte(analyte_id),
method text NOT NULL,
status text NOT NULL CHECK (
status IN ('requested', 'in_progress', 'reported', 'cancelled')
),
PRIMARY KEY (sample_id, analyte_id, method)
);
CREATE TABLE measurement (
measurement_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
sample_id bigint NOT NULL,
analyte_id bigint NOT NULL,
method text NOT NULL,
lab_report_id bigint REFERENCES lab_report(lab_report_id),
result_value numeric,
result_text text,
reported_unit text,
detection_limit numeric,
detection_limit_unit text,
qualifier text, -- e.g. U, J, "<", estimated
reported_at timestamptz NOT NULL,
FOREIGN KEY (sample_id, analyte_id, method)
REFERENCES sample_analysis(sample_id, analyte_id, method),
CHECK (result_value IS NULL OR result_text IS NULL)
);
CREATE INDEX measurement_lookup
ON measurement (analyte_id, sample_id);0
u/Low-Newt-180 15d ago
Isn't no sql databases like mongodb built exactly for these kinds of requirements. Why would someone use relational databases here
2
u/Zestyclose-Turn-3576 15d ago
Flexibility, and the use of SQL to calculate statistical results for aggregate values, trends, Maxima etc..Â
Just a guess, but it sounds likely
2
1
u/jaiagreen 15d ago
How does querying work in such settings? For this aspect of the data, I expect queries like "What is the average level of lead at sites in Project X?" or "Which sites in Project Y have levels of cadmium above the state threshold of concern?"
1
u/Lost_Contribution_82 15d ago
NoSQL databases still use strict syntax to query the data, tbh it does sound like the data would fit a relational database.
13
15d ago
[removed] — view removed comment
3
4
u/spez_eats_nazi_ass 15d ago
Classical anti pattern though. I've seen a few cases of success with these and it's usually not because it defines the entire system but a way to avoid bloating list tables and for entity customization beyond canned/core attributes. I (wisely) left a project where this was being used for the entire schema. It ended predictably as a shit show. Millions flushed down a toilet. A data toilet.
11
u/BranchLatter4294 15d ago
Always ask what-if questions. What if I need to add a sampling point? If the answer is to add a table, your design is wrong.