Mysql – Combine many table into one table in thesql table design

database-designmysql-5.6table

I'm grappling to design table relation. Version is Mysql 5.6.10.

My case is like below

bill table, payment table, contract table, recruitment tables… are existing.

Currently we're considering new function it will use bill or payment table. So I have designed new_function_table.

CREATE TABLE new_function_table`(
  id int unsigned not null auto_increment,
  bill_id int unsigned null default null,
  payment_id int unsigned null default null,
  primary key(id),
  index fk_new_function_table_bill_id_idx (bill_id asc),
  index fk_new_function_table_payment_id_idx (payment_id asc),
  constraint fk_new_function_table_bill_id foreign key (bill_id) references bill (id) on delete no action on update cascade,
  constraint fk_new_function_table_payment_id foreign key (payment_id) references payment (id) on delete no action on update cascade
)engine=innodb

Personally now is ok, but contract, recruitment, etc table be included into new_function_table in the future.

If new_function_table use 10 tables(bill table, payment table, contract table, recruitment tables…), then new_function_table has 10 index.
So i have designed like below It ignore data integrity.

CREATE TABLE new_function_table`(
  id int unsigned not null auto_increment,
  category tinyint unsigned not null comment '1: bill, 2: payment...',
  reference_table_id int unsigned not null comment 'bill_id or payment_id...';
)engine=innodb

What is the good design for this case? If you have an any idea please help me! Thank you.

Best Answer

I have designed with @Walter Mitty opinion.

CREATE TABLE new_function_table(
  id int unsigned not null auto_increment,
  category tinyint not null comment '1: bill, 2: payment...'
  primary key(id)
)engine=innodb

CREATE TABLE bill(
  id int unsigned not null auto_increment,
  new_function_table_id int unsigned default null,
  index fk_bill_new_function_table_id_idx (new_function_table_id),
  constraint fk_bill_new_function_table_id foreign key (new_function_table_id) references new_function_table (id) on delete no action on update cascade,
  primary key(id)
)engine=innodb

CREATE TABLE payment(
  id int unsigned not null auto_increment,
  new_function_table_id int unsigned default null,
  index fk_payment_new_function_table_id_idx (new_function_table_id),
  constraint fk_payment_new_function_table_id foreign key (new_function_table_id) references new_function_table (id) on delete no action on update cascade,
  primary key(id)
)engine=innodb
...etc

If it is wrong, then please feedback! Thank you.

Related Question