Where to store queries? (db view, application, .sql file, .lyr file)

Bud

I've got too many views in my GIS database. I'm starting to wonder if the database isn't the right place to store queries. Any tips?

1. Only store queries as database views if the view is spatial?
   1. ...since dragging views into the map is convenient; .lyr files in a network folder are less convenient.
2. Try not to have "views on top of views"? I.e., don't split views into manageable parts. Put all logic into a single big view to avoid clutter.
3. Store all other queries in the applications that use them -- i.e. reporting software such as Excel or Power BI?
4. Store the master query as a .sql file in a network folder?
5. Refactor queries so that they're simpler. And possibly eliminate some unnecessary queries. Get outside input from codereview.stackexchange.com or some other kind of code review.
6. Other?

How are things done in the non-GIS world? I'm starting to get the impression that people don't create many database views. Instead, logic is usually stored at the application level. Is that your experience? 

(ArcMap/Enterprise 10.7.1, ArcGIS Pro 2.6.8, Oracle 18c, SDE.ST_GEOMETRY)

 

JohannesLindner

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.