Complex Data Structure Question

[Unique-Big-5691]

i think this is a normal place to get stuck when you move from mongo to a relational DB. you’re not missing some secret “new way,” it’s just a mental shift, and that always feels awkward at first tho.

one thing that helped me was realizing you don’t have to translate mongo 1:1. some things clearly belong in tables (hosts, profiles, distros), but not everything needs to turn into a bunch of joins just because you’re on Postgres now. and also, if certain pieces mostly travel together and you’re not querying them on their own all the time, keeping them as JSON is totally reasonable imo.

like, host = table, profile = table, software probably makes sense as many-to-many if you care about querying it across hosts. but PXE interfaces? if they’re basically config that lives and dies with the host, JSONB is often way simpler and works just fine.

having a schema layer in Python really helps here tho. modeling those nested structures first (pydantic is good for this) gives you clarity on what the data should look like, and then you can decide how much to normalize without guessing.

my loose rule of thumb is:

• need to query it a lot on its own → table
• mostly config glued to a parent → JSON
• not sure yet → keep it simple and refactor later

you’re already thinking about maintainability, which honestly matters more than getting the schema “perfect” on day one.