Using views as a data interface between modules in a database

Question

Hello, I am working on the database layout of a straighforward small database in Mysql. We want to modularize this system in order to have more flexiblity for different implementations we are going to make. Now, the idea was to have one module in the database (simple a group of tables with constraints between them) pass its data to the next module via views. In this way, changes in one module would not affect the other ones, as we can make sure in the view that the right data is present there at any time, although the underlying structure of tables might be different.

The structure of the App handling the database would likewise be modularized.

Is this something that is sometimes done? On a technical side, as I understand views can't have primary keys - how would I then adress such a view? What other issues should be considered?

Answer 1

This is a valid approach, though you should of course be careful:

• Make sure that your queries are very well tested for performance with query plans analyzed. The views will use the underlying tables's indexes, BUT the likelyhood of bad execution plan is higher.

• Make sure that enough views exist to cover all needed info.

Answer 2

Is this something that is sometimes done?

Yes, views are often used to insulate things from data models in a state of flux.

On a technical side, as I understand views can't have primary keys - how would I then address such a view?

MySQL doesn't support materialized views. Non-materialized views are just prepared SQL statements - they don't exist in the data model, and performance is ultimately based on what exists on the underlying table(s) and query optimizations.

That said, layering views (creating a view that selects from another) is not recommended - it's brittle, and there's a big risk that performance will decrease because someone wants simplicity over query optimization.