Assuming I have multiple relations in my database, for example Store, Employee and Sale, and I want to connect pairs with a simple binary relationship. Personally I would create tables named Employee_Store and Employee_Sale with a natural key composed of the foreign keys.
Now, my colleague insists on creating one table for multiple relationships. For the above example there could be a table called EmployeeLinks:
IdLink int PK,
IdEmployee int FK null,
IdStore int FK null,
IdSale int FK null,
LinkType int not null
Please help me with good reasons why this is not a good idea. I have arguments of my own but I would like to keep them private and hear your unbiased opinions.
Initially the table above would have no primary key (!). Because the foreign keys allow null a surrogate key is the only option.