The following is a DB-only solution.
I assume your relations are:
- A customer has many branches
- A branch has many cashregisters
- A cashregister has many transactions
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:
- Any foreign key constraint needs an index in the child (referencing) and the parent (referenced) table, which can support the constraint check. The given column order in the keys allows us to define the schema with only one index per table.
- A foreign key should always reference a unique key in the parent table. However in this example the composition of referenced columns is (at least) implicitly unique, because it contains the primary key. In almost any other RDBMS you would need to define the indices in the "middle" tables (
branches and cashregisters) as UNIQUE. This however is not necessary in MySQL.
- The composite foreign keys will take care of the data integrity/consistency. Example: If you have a branch entry with
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.
- You will probably need more indices for your queries. Most probably you will need
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.)
- You can define relations like "customer has many transactions". Previously you would need to use "has many through", involving two middle/bridge tables. This will save you two joins in many cases.
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.
