Database refactoring
1 / 23

Database refactoring - PowerPoint PPT Presentation

  • Uploaded on

Database refactoring. For the beginning…. Avoid overspecialization. Barrier. Application developer. Database developer. Communication Cooperation Exchange of experience. Developer. Developer. Definition.

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
Download Presentation

PowerPoint Slideshow about 'Database refactoring' - dooley

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

For the beginning
For the beginning…

  • Avoid overspecialization


Application developer

Database developer

CommunicationCooperationExchange of experience




  • Refactoring of databases is the database schema change, improving its design, but not affecting its behavioral and information semantics.

  • It contains both structural and functional aspects

What can we refactor in db
What can we refactor in DB?

DB keeps:

  • Data (are kept according to the scheme)

  • Stored code

    The stored code doesn’t differ from any other code (it should be tested)

    The scheme of data (tables, indexes, etc.)

    The availability of data complicates the refactoring of the scheme!

Why should we refactor
Why should we refactor?

  • Correct the inherited database

  • Ensure the evolutionary development

  • Prevent the redundant design (over-design)

Smells of bad code in database
Smells of bad code in database

All the smells of codes can be applied to the stored code including:

  • Procedures-monsters

  • Spaghetti code

  • Code duplication

  • Oversaturation of the conditional operators

  • etc.

    Smells characteristic for DB:

  • Table/column of wide destination

  • Redundant data (repeated in several tables)

  • Table with a huge number of columns/rows

  • Smart columns (xml)

  • Lack of limitations (lack of validation)

  • Fear of changes in data scheme or procedure

Fear of changes
Fear of changes

  • The most «stinking»smell

  • Prevents the development

  • Reduces the effectiveness

  • Suggests an even bigger mess

  • Over time it becomes only worse

How to proceed
How to proceed?

  • Create your sandbox of development

  • Transfer changes in the sandbox of integration

  • Introduce in production


Frequent deployment

Controlled deployment

Highly controlled deployment

Worst case the most complex
Worst case (the most complex)

Trivial things
Trivial things

  • Is it possible to rename a column in the database without changing the hundreds of applications?

  • If we can not make such a triviality as we can do something really serious?

Unit tests in the database
Unit tests in the database

  • Is it too difficult?

  • Isn’t there a good testing tool (framework)?

Running unit tests
Running unit tests

  • Anonymous PL/SQL code

  • No need to change the database

  • Call raise_application_error a specific communication in case of an error

  • A rollback at the end of the test

  • Launch any SQL tools

Logs changes changelog
Logs changes (changelog)

  • You must track changes

  • Writing delta-scripts (migration):

    • At the beginning of the transition period

    • At the end of the transition period

  • The same scripts for

    • Update sandboxes

    • Deployment on the production


  • Developers

    • Close cooperation with DB administrators

    • Skills of operation with databases

  • Administrators of DB and designers of DB

    • Should be involved in developing the application

    • Skills of application development

Testing tools
Testing tools

  • Delta-scripts

    • Dbdeploy, liquibase, deltasql

    • It's easy to write yourself

  • PL/SQL code, Oracle SQL Devepoler, Intellij IDEA