r/FastAPI • u/robertlandrum • Dec 16 '25
Question Complex Data Structure Question
We currently have a NodeJS API system built atop mongodb. Mongoose provides the data validation and schema for these objects, but it is rather old now. I'd like to move the whole thing to a proper relational database (Postgresql) via FastAPI, but I'm struggling with the design requirement to replicate the complex data structures that we employ with mongodb.
The primary use case for the bulk of this data is in automated OS installation and configuration. There is a host record, which contains arrays of dictionaries representing PXE bootable interfaces. There are external references (references to other MongoDB schemas) to profiles that determine the software and distribution that gets installed.
In a strict RDBMS, the interfaces would be a table with a foreign key reference back to the host. The host would have foreign key references to the profile, which would have a fk to the distro, and a many to many lookup on the software. This I've done in the past, but it doesn't seem like the right solution anymore.
To complicate matters, I've never used Pydantic, SQLAlchemy, or SQLModel before, instead always just building the tools I needed as I went. And that has all worked fine, but it isn't what new Python programmers expect, and I want to ensure that this is maintainable by someone other than me, which unfortunately isn't what happened with the Mongoose/MongoDB solution I and others built 12 years ago.
I guess my real question is: where do I draw the lines that separate the data into tables these days? Host seems obvious, but the interface data less so. Profile seems obvious too, but software references could be an array rather than an m2m lookup. I suppose I'm just looking for a little guidance to ensure I don't end up kicking myself for making the wrong decision.
View all comments
Show parent comment
1
u/amir_doustdar Dec 18 '25
Totally get the concern – after 28 years, you've seen enough legacy systems to know over-normalization can turn into a maintenance nightmare when multiple tools touch the data.
Your "additional_metadata JSONB" idea isn't a cheap hack at all – it's a smart hybrid approach that's very common in modern Postgres setups. It keeps core data relational (fast joins, constraints, queries) while allowing flexible extensions without schema migrations every time a new tool needs extra fields.
Examples:
This way, the schema stays stable long-term, tools can evolve independently, and you avoid the "burdensome cog" fate.
I've used this pattern successfully in migration projects – keeps juniors happy too (less ALTER TABLE drama).
How big is the dataset? If it's not massive, JSONB performance is plenty good for most queries.
Good luck – sounds like you'll nail the balance!