foreign keys - Mysql several columns for polymorphic relationship - feasible? -

i have several 'modules'(at moment , 4) , each of has own table.

i want implement tag system these modules. tags have many many relationship between each module respectively.

to implement polymorphic relationship, thought of creating pivot table this:

tag_name|module_1_id | module_2_id| module_3_id |module_4_id... on.

any row of tag_module pivot table have valid fk 1 module_id , rest set null.

my question is, extremely hazardous way go implementation ? there ways far better want ?

how can ensure uniqueness of tag_name module_n_id , meaning particular item particular module should have specific tag assigned once ?

also, on time, number of modules increase , in future , possible number of columns 10,20 50. kind of implementation prevent kind of growth?

just create table module id , tag name fields , create multi-column unique index on these 2 columns (you can add other field table). multi column unique index allow associate same tag name multiple modules, prevent assigning same tag name same module multiple times.