mysql - Schema design for a table having one column pointing towards multiple tables -

consider following schema:

create schema testschema; use testschema;  create table (     id int not null auto_increment,     name varchar (50),     primary key(id));  create table b (     id int not null auto_increment,     name varchar (50),     primary key(id));  create table c (     id int not null auto_increment,     name varchar (50),     primary key(id));  create table main (     id int not null auto_increment,     typeid int,     type varchar(50),     tablemappingsid int,     primary key (id) );   create table tablemappings (     id int not null auto_increment,     tablename varchar(50),     primary key (id) );  insert (name) values  ('usa'), ('uk'), ('uno');  insert b (name) values  ('earth'), ('mars'), ('jupiter');  insert c (name) values  ('1211'), ('12543'), ('345');  insert main (typeid, type, tablemappingsid) values (1,'tablea',1), (2,'tableb',2), (3,'tablec',3);  insert tablemappings (tablename) values ('a'),('b'),('c'); 

description:-

i have 3 tables a, b , c have id , name.

in main table, type tells table (a or b or c) have read name property. typeid tells id within table(a or b or c). have created tablemappings table has tablenames. in main table have created column tablemappingsid points tablemappings.

is correct approach? , how can write query following in mysql:-

select (name property) table pointed row , mapped tablemappings?

about design

if think in object-oriented manner, have base class constituted of attributes recorded in main table, , derivated a, b , c additionnal attributes. want avoid having many attributes in single table nulls depending on records types. approach. method implement can improved.

answer question

you want select table (a, b or c) depending on value of field. far know cant done without "preparing" query.

"preparing" query can done in multiple manners :

• using prepared statements ("pure-sql" method) : https://dev.mysql.com/doc/refman/5.7/en/sql-syntax-prepared-statements.html
• in stored procedure or function example : selecting type, testing type , selecting right table
• or constituting query in 2 times via script language

example prepared statement :

set @idtoselect = 2;  select     concat('select name ', tablemappings.tablename, ' id = ', main.tablemappingsid)     @statement     main         inner join tablemappings on tablemappings.tablename = replace(main.type, 'table', '')     main.id = @idtoselect;  prepare stmt @statement;  execute stmt; 

note : have translate 'tablea', 'tableb'... in main.type match 'a', 'b'... in tablemappings.tablename, not ideal.

but not convenient , efficient.

other approaches , comments

selecting multiple tables : not big deal

basically, want avoid select'ing tables dont need read from. keep in mind if schema correctly indexed, not big deal. mysql runs query optimizer. left join of tables , select right table depending on 'type' value :

set @idtoselect = 2;  select     ifnull(a.name, ifnull(b.name, c.name)) name      main         left join on main.type = 'tablea' , a.id = main.tablemappingsid         left join b on main.type = 'tableb' , b.id = main.tablemappingsid         left join c on main.type = 'tablec' , c.id = main.tablemappingsid      main.id = @idtoselect; 

note didn't use tablemappings table

useless tablemappings trick

you can avoid using kind of mapping using same id in "children" table in main table. how many orm's implement inheritance. give example later in answer.

a bit irrelevant example

in question, want select "name" property regardless of type of record. bet if have different types of records, each type holds different set of properties. if "name" common property between types, should in main table. assume provided "name" simplified example. think in real case, you'll have select field regardless of type of object.

other thing : in data example, provide records a, b , c tables not match main records

final proposition

drop schema testschema; create schema testschema; use testschema;  create table main (     id int not null auto_increment,     typeid int,     common_data varchar(50),     primary key (id) );  create table (     id int not null,     specific_dataa varchar (50),     primary key(id),     foreign key fk_a (id) references main (id) );  create table b (     id int not null,     specific_datab varchar (50),     primary key(id),     foreign key fk_b (id) references main (id) );  create table c (     id int not null,     specific_datac varchar (50),     primary key(id),     foreign key fk_c (id) references main (id) );  insert main (typeid, common_data) values (1, 'abc'), (2, 'def'), (3, 'ghi');  insert (id, specific_dataa) values  (1, 'usa');  insert b (id, specific_datab) values  (2, 'mars');  insert c (id, specific_datac) values (3, '345'); 

some comments :

• typeid in main table optionnal, depending on queries have useful retrieve type of object. 1 field enough, dont need typeid integer , type varchar.

• id's in a, b , c tables not auto_increment because have match main id's

• this design irrelevant if there no common attributes, put common data field in main table

• i materialized relations defining foreign keys

queries examples :

i want common data field id 1 :

select common_data main id = 1; 

i know id 2 type b , want specific data b :

select specific_datab b id = 2; 

i know id 3 type c , want common data , specific data c :

select common_data, specific_datab main inner join b on b.id = main.id main.id = 2; 

(best match case) dont know type of object 3 want specific data depending on type :

select ifnull(     a.specific_dataa,     ifnull(         b.specific_datab,         c.specific_datac     ) ) main     left join on a.id = main.id     left join b on b.id = main.id     left join c on c.id = main.id main.id = 3