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:
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.