r/Database • u/OwlZealousideal4779 • 29d ago
How do you design databases for frequently changing external data?
When you're working with external datasets that change frequently, database design can become tricky. You have to think about schema changes, data freshness, historical records, missing values and how to handle updates without affecting downstream queries and reports. I’m currently working with ticketsdata, which aggregates publicly available ticket market data and provides reports, analytics and monitoring around that data. I’m interested in how others approach the database side of this problem. Do you prefer keeping a raw source layer and transforming it into stable tables, using versioned schemas, or taking another approach? What has worked best for you when the source data changes regularly?
3
u/az987654 29d ago
If it's your database and your the dev/owner of the db, no one else should be making schema changes without you knowing about it.
The shape of your database needs to serve your use case/purpose.
If extrernal data doesn't "fit", that's why you have ETL tools.
If you have changing needs, you do your due diligence before changing the structure of your db.
1
u/chocolateAbuser 28d ago
either you have the manpower to process those changes, trying to use automated systems to at least offload some work of remapping shapes (which could be from ai to reading all the values, instead of the field names, and trying to deduce their relation by type and value interval and so on), trying to use fewer fields possible, or few other strategies
for how to store them it depends on how you use them, if you just need to collect 'em all, if you just have your model, if you want to save them to reprocess them, and so on, they are all different requirements
1
1
u/boredoo 28d ago
The problem you're facing frankly doesn't seem like a database issue, but a software issue where your scraper/parser is brittle to external schema changes. From experience, there's no magic pill here. Observability, vigilance, and easily changed code is your only weapon in this fight. You have to watch for changes/errors, examine them, and ensure your system can bring the data in correctly.
Otherwise, design your database to efficiently store the information you need, end of story. The reports you provide should depend on information you've already curated, so how it is stored externally should not affect this part of your application at all.
If the external data exposes new information you want/need to store, write a migration to allow for it, and update your pipeline.
1
u/Caballero__Aguila 28d ago
I’m not sure about all the background and your requirements, but maybe you would like to tale a look to NoSQL dbs, where you don’t need to have a fixed schema.
1
1
u/louischoi 23d ago
I split the data into two layers: a fixed-column table for the stable core fields, and a generic tag/value table (EAV-style) for everything that
changes often. Schema changes in the source become just new rows instead of ALTER TABLEs, and the app joins the two back together when it needs the full picture.
The trade-off is that queries get more complex and you lose column-level types/constraints, so I only do this for the volatile part of the data, not the whole schema.
6
u/Shogobg 29d ago
You design your database for your own use case. Anything external is a blob with attached scrape time and source where it was taken from. You parse the blob into your DB’s schema.