create tabel good(
id int not null,
name varchar( 200 ) not null,
customer_id int not null,
constraint good_pk primary key (id),
constraint good_fk foreign key (customer_id) references customer (id));
create table good_measure(
id int not null,
measure_type char( 1 ) not null,
name varchar( 10 ) not null,
constraint mg_pk primary key (id),
constraint mg_chk check (measure_type in ('I','D')),
constraint mg_uk unique (id, measure_type));
create table good_quantity(
good_id int not null,
measure_id int not null,
measure_type char( 1 ) not null,
constraint gq_pk primary key (good_id),
constraint gq_chk check (measure_type in ('I','D')),
constraint gq_fk1 foreign key (good_id) references good (id),
constraint gq_fk2 foreign key (measure_id, measure_type) references good_measure (id, measure_type),
constraint gq_uk unique (good_id, measure_id, measure_type));
create table good_quantity_int(
good_id int not null,
measure_id int not null,
measure_type char( 1 ) not null,
quantity int not null,
constraint gqi_pk primary key (good_id),
constraint gqi_fk foreign key (good_id, measure_id, measure_type) references good_quantity (good_id, measure_id, measure_type),
constraint gq_chk check (measure_type = 'I'));
create table good_quantity_dec(
good_id int not null,
measure_id int not null,
measure_type char( 1 ) not null,
quantity decimal( 10 , 3 ) not null,
constraint gqi_pk primary key (good_id),
constraint gqi_fk foreign key (good_id, measure_id, measure_type) references good_quantity (good_id, measure_id, measure_type),
constraint gq_chk check (measure_type = 'D'));