I have a control table that looks like this:
CREATE TABLE `tbl_control` ( `tm_db_tbl` varchar(64) NOT NULL, `tm_key` int(11) unsigned NOT NULL, /* a bunch of other fields here */ PRIMARY KEY (`tm_db_tbl`,`tm_key`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
From the perspective of my other tables, when I want to join on this control table, I join on the
tm_key in my table along with a string literal designating my database and table against the
tm_db_tbl field above. With the client table below, I would join
WHERE ct.tm_db_tbl='my_db.some_client_table' AND ct.tm_key=sct.tm_key.
CREATE TABLE `some_client_table` ( `tm_key` int(11) NOT NULL, /* a bunch of other fields here */ PRIMARY KEY (`tm_key`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
This is a tiny bit of optimization so I don't have a
tm_db_tbl column in my client table with all the same values. The relationship here is many-client-tables-to-one-control-table.
But now, I run into a bit of a problem when I want to define a
foreign key on my client table because the corresponding
tm_db_tbl is a string-literal.
Should I just bite-the-bullet and add
tm_db_tbl filled all with the same value to my table or is there a better way around this problem?
If it makes any difference, I am using mysql.