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;
}
}