2

I have a quick question with respect to many to many relationships in sql. So theoretically i understand that if 2 entities in an ER model have a M:N relationship between them, we have to split that into 2 1:N relationships with the inclusion of an intersection/lookup table which has a composite primary key from both the parent tables. But, my question here is , in addition to the composite primary key, can there be any other extra column added to the composite table which are not in any of the 2 parent tables ? (apart from intersectionTableId, table1ID, table2ID) a 4rth column which is entirely new and not in any of the 2 parent tables ? Please let me know.

Mureinik
  • 297,002
  • 52
  • 306
  • 350
gireesh4manu
  • 109
  • 2
  • 12
  • What DBMS is this and how is the intersection table being created? – SS_DBA Oct 17 '16 at 19:34
  • 2
    Absolutely! Data about the association BELONGS on such a table. Think about a Customer / Movie rental database. I want to know what customer checked out what movie as a customer could have many movies and a movie could be checked out by only 1 customer at a time; but I want the history of the movies checked out and know when it's been returned. The associative table could have a Checkout and a CheckIn date; this would tell me the history and duration of checkouts for a customer and maybe based on movie genera and customer rented movies, a suggestion listing could be created... – xQbert Oct 17 '16 at 19:41
  • @WEI_DBA, I was using Oracle at the time and it was for a book recommendation table just in case the person couldn't find his/her required book, we wanted to suggest another suitable one, my apologies for the delay in response. – gireesh4manu Aug 03 '18 at 07:12
  • Thanks a lot @xQbert for clearing this up in my head, I apologize for not having followed up on this, but i'm learning from my past mistakes. Hope you can let that pass. – gireesh4manu Aug 03 '18 at 07:13
  • @gireesh4manu no problem.Just glad to help. – xQbert Aug 03 '18 at 12:19

2 Answers2

4

In a word - yes. It's a common practice to denote properties of the relationship between the two entities.

E.g., consider you have a database storing the details of people and the sports teams they like:

CREATE TABLE person (
    id INT PRIMARY KEY,
    first_name VARCHAR(10),
    last_name VARCHAR(10)
);

CREATE TABLE team (
    id INT PRIMARY KEY,
    name VARCHAR(10)
);

A person may like more than one team, which is your classic M:N relationship table. But, you could also add some details to this entity, such as when did a person start liking a team:

CREATE TABLE fandom (
    person_id INT NOT NULL REFERENCES person(id),    
    team_id INT NOT NULL REFERENCES team(id),
    fandom_started DATE,
    PRIMARY KEY (person_id, team_id)
);
Mureinik
  • 297,002
  • 52
  • 306
  • 350
  • Thank you for your response @Mureinik. Appreciate it big time. it was basically for a book recommendation feature to suggest alternate books for folks who couldn't find out the required ones. – gireesh4manu Aug 03 '18 at 07:15
0

Yes, you can do that by modeling the "relationship" table yourself explicitly (just like your other entities).

Here are some posts about exactly that question.

Create code first, many to many, with additional fields in association table

Entity Framework CodeFirst many to many relationship with additional information

Community
  • 1
  • 1
SlimsGhost
  • 2,849
  • 1
  • 10
  • 16