Help wrapping the mind around many-to-many relationships

relational-theory

I'm making a database that needs to have the following tables: Article, Video, Image, and Keyword. Keyword contains a fixed list of keywords. (For example when creating an article, you'd have to select from a list of keywords, not create a new one) Each Article, Image, and Video can have n number of keywords. I'm having a hard time wrapping my mind around how I'd define these relationships in my database, as I've really only dealt with one-to-one and one-to-many in the applications I've had to develop in the past. Would I do a many-to-many between Keyword and the other tables? Or would I need to make a join table between each table? KeywordImage (keyword_id, image_id)? Or are neither of those options the right way to think about this?

If a join table is the right approach, would it be one-to-many between Article and ArticleKeyword?

edit
Keyword isn't a required field in any of the tables

Best Answer

Something like:

Article                ArticleKeyword         Keyword
---------------        ---------------        ------------
article_id (PK)  <---  article_id (FK)        
                       keyword_id (FK)  --->  keyword_id (PK)

There could be zero-to-many ArticleKeyword rows for each row in Article.

There could be zero-to-many ArticleKeyword rows for each row in Keyword.

This equates to a many-to-many relationship between that Article and Keyword tables.

The primary key for ArticleKeyword would be the composite key (article_id, keyword_id) unless you wanted to count multiple occurrences of the same keyword in the same article separately (perhaps noting the position in the article) in which case you may instead want a surrogate key like so:

Article                ArticleKeyword         Keyword
---------------        ---------------        ------------
                       ak_id      (PK)        
article_id (PK)  <---  article_id (FK)        
                       keyword_id (FK)  --->  keyword_id (PK)
                       kw_location

(or you could add the location properties to the combined key, of course, if those extra columns would never be NULL)

Related Solutions

How to design relationships for variant data

This might force a bit of change for your tables, but we do something like this at work:

Ensure each table has a int/guid primary key of some sort:

Organization Table

ID | Name
1  | MyOrganization

Then create a type-table to house your different LeadSource types:

LeadSourceTypeKey | Description
1                 | Organization
2                 | Company Office

Then on your ProductSoldList Table add 2 columns LeadSourceID and LeadSourceType

Then you can easily query the ProductSoldList TABLE and know which LeadSource to join back to:

SELECT *
FROM   [ProductSoldList] PL
       INNER JOIN [Organization] O
       ON PL.LeadSourceID = O.ID AND
          PL.LeadSourceType = 1
       INNER JOIN [Company Office] CO
       ON PL.LeadSourceID = CO.ID AND
          PL.LeadSourceType = 2

Is it better to use multiple one-to-many relationships, or polymorphic

The question you should ask yourself is whether or not a website click is really the same as an ad click. Do they have exactly the same predicates? (other than what type of parent they have) Similarly, when you access click data, will both types often be considered together or are they always treated separately?

The reason that this is important is that you want to draw a distinction between a true subtyping situation and simply having two distinct tables with similar schemas. Someone with a programming background might be tempted to say: "These two things look alike, so they should be like implementations of an Interface." This leads you to a polymorphic design.

However, the considerations for entity subtyping are not quite the same as the considerations for code re-use. If there is no reason to comingle both types of clicks in a single query, then there isn't necessarily a good reason to comingle both types of clicks in a supertype table. There may be a trade-off to be made between a bit of code reuse and keeping your data organized for convenient and logical retrieval.

Best Answer

Related Solutions

How to design relationships for variant data

Is it better to use multiple one-to-many relationships, or polymorphic

Related Question