Refactoring a database using VIEWS

Asked

Viewed 99 times

0

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

I wonder if anyone has ever done something like this and whether this strategy with VIEWs is feasible to refactor a database as described.

PS1: I intend to build the VIEWs following the instructions of part of the documentation, which defines when the VIEWs sane insertable/updatable.
The current BD has 150+ tables, and some tables have up to 50+ columns. The system is written in PHP without any standard or architecture. There are several JOINs, Some make sense and some don’t, and the intention will be to rewrite most of the queries, because their performance is deplorable.
The bank does not follow any normalization standard, but since it is a huge structure, I believe it will generate a somewhat complex structure in these terms after remodeling it.
Another thing to consider is that the new structure and the old one will inhabit the same database, I will only be able to get rid of the old structure at the end of the project, when it is already completely obsolete.
The DAO should ideally support both the physical tables and the VIEWs, but I believe I could create a DAO for each case.

PS2: The library you want to use will be the 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 VIEW, 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 VIEW which would be the actual table already remodels:

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;
    }
}
  • views generally do not allow Insert, so it would not work in its fullness, depending on the DBMS or DAO implementation, one can use the view to read, and the actual table to write. In postgres one can create actions to treat an Insert in a view, I do not know the existence of this in mysql. Maybe if we detail a little more we can help you.

  • @Gregoriobonfante I will update the question by clarifying a little more, taking into account your considerations

  • @Gregoriobonfante Would a DAO for VIEW and a DAO for the physical table be better?

  • 1

    So, n know how is the implementation that you are using, as an example I will speak of something more common as the Laravel, in it you can modify the foma as it is worked (with a medium effort) and in the same model use a view for reading and a table for writing. Everything depends on the solution, I can’t know how much will change from a table to view, if it forms simple changes, can do in any one ( how to separate a table in several views or the reverse, where several Tables become a view).

  • My suggestion, rewrite the structure, once you have a similar structure ( without considering visual), migrate the data that already has 'place'. depending on the time available test to see if this 'valid' make the migration. Measuring time is important, but in many cases the main problem is below. I have worked with poorly structured banks, I say that in some cases it was worth using less laborious techniques, to speak of some solutions clear that depends on the points you want to optimize, work on 'frontend' how to reduce css/js and use cache is a good.

  • In the queries can also use cache, especially in those more time consuming. The views of a database however much they help end up being 'query saved', being more worthwhile to use them when you can filter part of the data, as remove some records that do not exist in other tables etc.. Otherwise it turns out to be a mere masquerade. In the case of a production system where there is little time to improve, I recommend going to the easy as cache, incremental views (Physical tables fed by actions in other tables).

  • If possible, provide part of the table structure and its access implementation. both current and intended. I believe it would be easier to help you.

  • First, thanks for the help so far. I decided to use this library to access the data. The current access is done directly by the mysql_* functions and sometimes mysqli_*. I think if I provide part of the structure I’m going to break some privacy rules, then I’m going to replicate with the utmost fidelity the actual structure.

  • a good alternative, there are other solutions that rely on more employees, if you seek an active record, try the Latin, the eloquent used by it uses the same Latin.

  • I actually decided on the library not taking into account the Pattern used, but rather the flexibility/ease of use. I’ve already started the project and unfortunately I can’t commit to learning curves. In fact, I’m looking for a very thin layer of data access, not something so robust that I can change databases or something like that, because I know it’s not going to happen at all.

  • Actually now I realized that what I’m looking for is a code abstraction that allows me, if when the VIEW is not updateable/insertable, I can use the real table. Perhaps an Entity abstracting this, or even two, if need be. You would have something in mind?

  • So it depends on the case. You get this with Doctrine via event, the Eloquent too, are different Patterns. Although the curve with Eloquent is simpler I don’t really like the approach. But in Eloquent you can see that the Class Model has methods like the performInsert, in it you could change the table. Just understand that depending on what you want the smallest curve can have a price at the end. As I said before, it also depends on the difference between Views and Tables

  • A faster alternative, use models for creating and updating and read-only actions.

Show 8 more comments
No answers

Browser other questions tagged

You are not signed in. Login or sign up in order to post.