Subject Altering views
Author Daniel Miller
This is a bit of a philosophical question - as such there are probably many
opinions. But I'll ask anyway.

With relational databases there can be associations between tables that are
often repeated to where it is desirable to store and re-use them. During
app development, the use and therefore the structure of the relevant
database often changes. The problem that prompted this question:

I have several tables linked via primary & foreign keys. Surprise. Their
relationship is such (one-to-many and one-to-one) that usually joins are
required. The fact that Views are (or can be) manipulated transparently vs
tables caused me to setup a number of them. I also have some views that are
dependent on other views. One of those had a trigger defined to make the
view updateable. One of those app changes I mentioned above came up - and
via Flamerobin I attempted to modify the view. The fact that Flamerobin
seemed to lock up during this attempt, and my trigger being dropped without
recovery (I guess no rollback for that), is secondary.

From the usage point of application development and no end user direct
interaction with the database - preferably with specific arguments as to
why - I'm asking what is "better" for reusable SQL. And whether the same
statements/structures should be used for both retrieval and updates.
Options I can think of:

- SQL statements defined in app - possibly via constants or other central
- Views
- Stored procedures