Best practices to isolate data between database and UI

In a simple Phoenix app the data in the UI is closely related to the database structure.

However when things start to get complex the data in a single UI element comes from distinct tables.

Example 1, I want to show a list of appointments. Each appointment has fields like date, patient, type, resources, etc. But each field comes from a distinct table.

Example 2, I want to edit a form which has a lot of fields. However the fields in the UI are not columns in the same database row, instead each field is a row in a table.

Since my project is fairly complex I have an umbrella with multiple web apps and one ecto app.

I experimented adding another layer between ecto and web that handles the business logic.

Lately I was thinking in creating the UI structs in the view folder of the web app.

I feel that I am running in circles or perhaps unnecessary complicating my app structure.

Any hints in how to structure the app?

Sounds like you might be overthinking your application design. Phoenix is your web UI layer, Postgres is your database layer, and Ecto is the bridge between the two. The Ecto.Schema module maps your data structures to your database tables so you can easily move data from your database to your UI.

If you designed your database correctly Ecto should allow you to create mappings to your database and then use associations to connect related data. So if you have an appointments table and a patients table you should be able to connect them through associations and then display them in your UI. You should also be able to edit related data as well. Perhaps you need to simplify your app and learn more about Ecto.