Does views use an all-crazy idea?

Question

I'm working on creating a MySQL database, and I think that instead of coding a bunch of complex join requests in an interface, I will create a view for any queries that I need, and then all the interface codes, execute simple SELECT whatever FROM some_view WHERE something=5; queries SELECT whatever FROM some_view WHERE something=5; .

This seems like a great idea, because it abstracts the main scheme, so you don’t need to know about it, and given that MySQL can combine views into queries, I think this will be no less effective than a more direct solution.

Now to the question: Is this a stupid idea for some reason, I don’t see?

---

Note. It will go only on two layers, ei representations will refer only to tables, never be displayed.

Answer 1

Views can simplify the amount of text you need to create a query, but layering each other is bad practice .

This encapsulation also jeopardizes poorly executed queries, because the views must be executed before they can join each other - all this logic inside may not match what you need for the final result, so being lazy can easily mean a query that doesn't performs as well as it should.

Since views are queries that are run only on invocation, you will not be aware of missing links until they are executed.

Remember that when using SELECT * in a view, the database captures the list of columns when the CREATE VIEW statement is executed - if the columns change, you need to update the view to get the changes.

There is also no performance difference between the presentation and the launch of the query on which the view is based. With the exception of materialized views (which MySQL does not support), views are just a prepared statement. If simple enough, the WHERE predicates can be transferred from the FROM view WHERE .... to the internal query, but this means the lack of function use and unreliability.

Conclusion

OK, but be careful.

Answer 2

If you do not embed your views, I am of the opinion that it is a good idea to code access data in this way. (Remember that MS has been saying for many years that CRUD should be done through sp)

Not everyone agrees with me, but I like the abstraction of the data model this way ... thinking about DB calls as methods. Although I try to do this using stored procedures, it simplifies data model changes and provides transparency for requests to applications that use data.

Personally, I think that it forces application developers to treat SQL as declarative (as expected), because you remove their ability to treat it differently.