I'm working on a project, and we've been creating tables that store references as a combination of two columns.
An example: we have an 'alerts' table, that is used to deliver information to the user. An alert can refer to a number of different things, you can be alerted about a new message, or if another user has mentioned you. These alerts have different types, and reference different types of data. The message alert should direct the user to the message page, and the mention alert should direct the user to the other users page.
We're using columns alert.type and alert.article and storing type = MESSAGE or type = MENTION and article as the foreign key to either the message or user table. This strikes me as sort of poor design, squishing references to two different tables into the same column. However, since our tables are InnoDB I've had a tough time justifying this. Hopefully I can get some expert input as to why this is a good/bad choice. I'm thinking this approach could get into problems with indexed joins, but I really don't know enough about that.
I guess the other options are a column per type, which makes the table quite wide, and full of nulls, which I don't really mind, but some find tedious; or a 'child' table for each type which seems the most correct, but a bit overkill?
Cheers,