Hacker News Reader

Comment by brigandish | Hacker News Reader

Good point. Using stored procedures / views etc will help crystalise the API for the DB and allow work to happen behind that wall without breaking anything else in the meantime too. Once the work is done, bits of the wall can be replaced with better bits of wall i.e. improved sp's and views pointing to an improved schema.

View on HN

Yep. views in RDBMS are much underrated, IMO just because they are old tech. It should be possible to use views to:

1) normalise the database (fold these ugly add-on tables as columns in the parent table, with suitable null constraints, then drop the add-on table).

2) Use views to add the add-on tables (now views) back in again.

3) Continue running the old application code against the views, which present the old, ugly schema.

4) Simultaneously write the new code against the normalised core tables.

This use allow to update SELECT operations, but UPDATE / DELETE still have to be done on the old underlying schema right ?

No way to trigger a DELETE from a view right ? How would you approach this ?

Views can be updatable though there are caveats, but deletes and updats can be done via a function or stored procedure, meaning there's no direct access by the app to the underlying schema. If it's done well it means that the calling code in the app won't have to change (or at least be minimised) even as the schema changes. The speed and security benefits are a nice by-product.

You can even do things like prevent the app from deleting things unless the functions are used and prevent poor development practices in the process.

Reply on news.ycombinator.com