In my case:
I built and maintain my geodatabase, my colleagues don't know its structure. It's in third normal form, so information about a single object has to be collected from multiple tables. This is impossible without knowing the database structure.
It has to be easy for my colleagues to get the information they want. It has to be easy for me to republish services.
That means:
- There's a view stored in the database for every real world object type. Some object types have a view for internal use and a view for use in external departments or for the public. My database contains more views than tables.
- The queries are actually stored in a Python module in a network folder. This way, I can recreate the views automatically if needed, and I can export the query and the view's description to my documentation. I don't think that's best practice, but it works...
- Views that I need on the fly I just create inside a project using MakeQueryLayer.
- Yes, I avoid "views on top of views", because that would add clutter to the database and thus make it harder for my colleagues to get the information they want. Sub queries that are used by multiple views are handled in the Python module. The actual database views contain all neccesary logic, no queries of other views.
I don't have to interface with non-GIS software, so I can't tell you what's best practices there.
Have a great day!
Johannes