Refactor a database using VIEWS

0

I have the task of refactoring a database and would like to know if VIEWs are feasible for the task. My strategy is to create a VIEW by imitating the new structure of the supposed table, at least the name of the table (name of VIEW ) and fields (column aliases), until the changes are confirmed and I can create the% in fact%. The concern I have is about the performance and feasibility of integrating TABLE with the VIEWS layer, which I am also implementing. The DAO layer will implement anyway, whether it's reflecting DAO or VIEWS , and I've chosen TABLES to just impact it little or not at all.

I would like to know if someone has done something similar and if this strategy with VIEWS is feasible to refactor a database as described.

PS1: I want to build VIEWs by following the instructions in this part of the documentation , which defines when VIEWs are insertable / updatable . The current DB has 150+ tables, and some tables have as many as 50+ columns. The system is written in PHP without any standard or architecture. There are several VIEWs , some make sense and others do not, and the intention will be to rewrite a lot of queries, because their performance is deplorable. The bank does not follow any standard of normalization, but as it is a huge structure, I believe it will generate a somewhat complex structure in those terms after remodeling it. Another thing to consider, it will be that the new structure and the old will inhabit the same database, I will only be able to get rid of the old structure at the end of the project, as long as it is completely obsolete. DAO ideally should support both the physical and%% tables, but I think I could create a DAO for each case.

PS2: The library you want to use will be ActiveRecord
DDL of part of a current table:

CREATE TABLE Usuarios {
    'id' int(11) NOT NULL AUTO_INCREMENT,
    'user_name' varchar(16) NOT NULL DEFAULT '',
    'user_pass' varchar(255) NOT NULL,
    'flag_ativo' varchar(255) NOT NULL DEFAULT 'ON' #ativo ou nao varia entre ON e OFF, respectivamente
}

Example of a JOINs , where the result would reflect the restructured table:

SELECT
    'Usuarios'.'id' AS 'cd_usuario',
    'Usuarios'.'user_name' AS 'de_usuario',
    'Usuarios'.'user_pass' AS 'de_senha',
    IF (('Usuarios'.'flag_ativo' = 'ON'), 1, 0) AS 'fl_ativo'
FROM
    'Usuarios'

An intended DAO, reflecting the VIEWs that would be the actual table already reshapes:

class Usuarios extends ActiveRecord\Model {
    //table_name é um atributo da ActiveRecord\Model
    static $table_name = 'tb_usuarios';

    //Também atributo da ActiveRecord\Model
    static $primary_key = 'cd_usuario';

    //De uso interno da classe, faria isso para cada campo
    static $de_usuario = 'de_usuario';

    static $de_senha = 'de_senha';

    static $fl_ativo = 'fl_ativo';

    public function validaLogin($user, $pass) {
        $usuario = self::first(
                     array('conditions' => 
                         array(
                             self::$de_usuario.' = ? AND '.
                             self::$de_senha.' = ? AND '.
                             self::$fl_ativo.' = ?', 
                             $user, 
                             md5($pass),
                             1
                         )
                     )
        );
        return $usuario !== null;
    }
}
    
asked by anonymous 05.09.2017 / 19:42

0 answers