What is the best design approach: having separate link / association tables for each data type in the database, or including a common identifier in a common link / association table?
Since this question really does not make sense without an example ...
Suppose I have a database that contains data for authors and books (to use an example with which people can easily understand and identify). For simplicity, the tables for each are as follows:
Authors
Id
Name
Books
Id
AuthorID
Name
ISBN
I decided to include links for these two datasets in other systems (Amazon, Barnes and Noble, Books-A-Million, etc.). I created tables for LinkTypes, LinkSources and LinkBases:
Linktypes
Id
Name (e.g. book, author)
LinkSources
Id
Name (e.g. Amazon, B&N, etc.)
communication bases
Id
LinkTypeId
LinkSourceId
UrlBase (for example, http://www.amazon.com/book.html?id= {0})
And the table for the actual links:
References
Id
LinkTypeId
LinkSourceId
ReferenceValue (this value is replaced with the associated UrlBase to create a real link)
So far, I have been thinking of creating separate tables for links related to authors and books. The tables look something like this:
AuthorLinks
AuthorID
LinkId
, , , , , , , , ? ( )?
, , Links, AssociationId, , . , .
, ? , .