We have tables in our database with hundreds of columns (with some, but not much referential integrity).
In my web App I want to show a list of entities (e.g. a list of customers) - so I really only want to bring down the Id, name and possibly a couple of other columns.
When the user selects the customer, I might then call another API to get additional details for display and/or update.
Other areas of my apps may require different sub-selections of data. Some may require joins to other tables (e.g. join to CustomerType table) others not.
There are many techniques we could use to achieve this - what I am looking for is your experiences - what has (or hasn't) worked for you.
What I have tried:
Defining many small classes for return (Customer, Customer_List, Customer_Detail_List)
Always bringing the whole Customer data, together with any referential field data.
Defining many SQL Views, (Customer_List, Customer_Detail_List) and using an ORM to return from each.