Combine many table into one table in mysql table design

Question

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.

• Akina: So i have designed like below It ignore data integrity. Integrity check MUST exist. How do you want to check it using the structure designed? If new_function_table use 10 tables ... then new_function_table has 10 index. It is a common practice. Currently we're considering new function it will use bill or payment table. I can't think of an ENTITY that has the properties shown. Everything looks like this up-structure is synthetic.
• jonggu: @Akina Thank you for your reply. Right! up-structure is synthetic. you are saying "It is a common practice". Table has 10 index. Is it ok? I suppose DML speed is not good.
• Akina: Table has 10 index. Is it ok? it has 10 external references - so it MUST have 10 indices for this references maintainance. I suppose DML speed is not good. But where to go? EAV up-structure has the same problems, but in SELECTion - and in most cases they are the most part of total queries stream. Plus integrity maintainance problems - which are additional DML problems too.
• Walter Mitty: Shared primary Key is a design technique for using a single identifier in multiple tables, such that one table can reference another. For more details, view this.
• Walter Mitty: Class Table Inheritance is a design technique for dealing with the generalization/specialization (aka superclass/subclass) case. For more details, view this.

Answer 1

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.