Hey everyone!
I have these 2 tables and I am trying to design it in a way that when a product is deleted from the catalog it also get its entry removed from the installation table.
The situation is: each installation has its own products list on it. but what if a product is no longer available? the installations having that product should remove it from the list.
Appreciate any help!
create table products
(
id int unsigned auto_increment not null primary key,
description varchar(64),
price float(5,2) unsigned
)type=InnoDB;
create table inst_products
(FOREIGN KEY (inst_id) references installation(inst_id) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (descID) references products(id) ON DELETE NO ACTION,
inst_id int unsigned not null,
unints smallint unsigned,
descID int unsigned not null
)type=InnoDB;