Question to all Yii2 normalization geeks out there.
Where is the best place to set non-normalized columns in Yii2?
Example, I have models Customer, Branch, CashRegister, and Transaction. In a perfect world, and in a perfectly normalized Database, our Transaction model would have only the cashregister_id, The CashRegister would store branch_id, and the Branch would store customer_id. However due to performance issues, we find ourselves obliged sometimes though to have a non-normalized Transaction model containing the following:
When creating a transaction, I want to store all 3 values. Setting
$transaction->branch_id = $transaction->cashRegister->branch_id;
$transaction->customer_id = $transaction->cashRegister->branch->customer_id;
however in the controller does not feel correct.
One solution would be to do this in aftersave() in the Transaction model and make those columns read-only. But this also seems better but not perfect.
I wanted to know what is the best practice or where is the best place to set those duplicate columns, to make sure that the data integrity is maintained?
The following is a DB-only solution.
I assume your relations are:
The corresponding schema could be:
create table customers (
customer_id int auto_increment,
customer_data text,
primary key (customer_id)
);
create table branches (
branch_id int auto_increment,
customer_id int not null,
branch_data text,
primary key (branch_id),
index (customer_id),
foreign key (customer_id) references customers(customer_id)
);
create table cashregisters (
cashregister_id int auto_increment,
branch_id int not null,
cashregister_data text,
primary key (cashregister_id),
index (branch_id),
foreign key (branch_id) references branches(branch_id)
);
create table transactions (
transaction_id int auto_increment,
cashregister_id int not null,
transaction_data text,
primary key (transaction_id),
index (cashregister_id),
foreign key (cashregister_id) references cashregisters(cashregister_id)
);
(Note: This should be part of your question - so we wouldn't need to guess.)
If you want to include redundant columns (branch_id and customer_id) in the transactions table, you should make them part of the foreign key. But first you will need to include a customer_id column in the cashregisters table and also make it part of the foreign key.
The extended schema would be:
create table customers (
customer_id int auto_increment,
customer_data text,
primary key (customer_id)
);
create table branches (
branch_id int auto_increment,
customer_id int not null,
branch_data text,
primary key (branch_id),
index (customer_id, branch_id),
foreign key (customer_id) references customers(customer_id)
);
create table cashregisters (
cashregister_id int auto_increment,
branch_id int not null,
customer_id int not null,
cashregister_data text,
primary key (cashregister_id),
index (customer_id, branch_id, cashregister_id),
foreign key (customer_id, branch_id)
references branches(customer_id, branch_id)
);
create table transactions (
transaction_id int auto_increment,
cashregister_id int not null,
branch_id int not null,
customer_id int not null,
transaction_data text,
primary key (transaction_id),
index (customer_id, branch_id, cashregister_id),
foreign key (customer_id, branch_id, cashregister_id)
references cashregisters(customer_id, branch_id, cashregister_id)
);
Notes:
branches and cashregisters) as UNIQUE. This however is not necessary in MySQL.branch_id = 2 and customer_id = 1 - you wan't be able to insert a cashregister with branch_id = 2 and customer_id = 3, because this would violate the foreign key constraint.cashregisters(branch_id) and transactions(cashregister_id). With these indices you might not even need to change your ORM relation code. (though AFAIK Yii supports composite foreign keys.)If you want the redundant data to be maintained by the database, you can use the following triggers:
create trigger cashregisters_before_insert
before insert on cashregisters for each row
set new.customer_id = (
select b.customer_id
from branches b
where b.branch_id = new.branch_id
)
;
delimiter $$
create trigger transactions_before_insert
before insert on transactions for each row
begin
declare new_customer_id, new_branch_id int;
select c.customer_id, c.branch_id into new_customer_id, new_branch_id
from cashregisters c
where c.cashregister_id = new.cashregister_id;
set new.customer_id = new_customer_id;
set new.branch_id = new_branch_id;
end $$
delimiter ;
Now you can insert new entries without defining the redundant values:
insert into cashregisters (branch_id, cashregister_data) values
(2, 'cashregister 1'),
(1, 'cashregister 2');
insert into transactions (cashregister_id, transaction_data) values
(2, 'transaction 1'),
(1, 'transaction 2');
See demo: https://www.db-fiddle.com/f/fE7kVxiTcZBX3gfA81nJzE/0
If your business logic allows to update the relations, you should extend your foreign keys with ON UPDATE CASCADE. This will make the changes through the relation chain down to the transactions table.
I had similar problem once and using afterSave() or beforeSave() looked as a great solution at the beginning, but finally resulted hard to maintain spaghetti code. I ended up with creating separate component for managing such relations. Something like:
class TransactionsManager extends Component {
public function createTransaction(TransactionInfo $info, CashRegister $register) {
// magic
}
}
Then you're not creating or updating Transaction model directly, you're alway using this component and encapsulates all logic in it. Then ActiveRecord works more like a data representation and does not contain any advanced business logic. It looks more complicated in some cases than $model->load($data) && $model->save() but after all it is much easier to maintain when you have all logic in one place and you don't need to debug save() calls chains (one model runs save() of different model in afterSave() which runs save() of different model in afterSave()... and so on).