Separate reference / association tables for different data?

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

, ? , .

+1
2

: "--"

, . ( Item, , ), Author, Book - , . - :

Item      Author    Book       LinkBase        Link
=======   =======   =========  ==========      ========
ItemID    ItemID    ItemID     LinkBaseID      LinkBaseID
          Name      AuthorID   LinkTypeId      ItemID
                    Title      LinkSourceId    ReferenceValue
                               ReferenceValue

, LinkSourceID Link, LinkBase, LinkSourceID.

, , , Links AssociationID.

+1

, RDF, .. . , . , , , .

0

Source: https://habr.com/ru/post/1752641/


All Articles