Search code examples
mysqldatabasedatabase-designmany-to-manyrelational-database

Should junction tables have more than one primary keys from another identifying table?


Here's an example: Originally I have 3 tables. Table B references Table A. So now Table B has two primary keys. One used as the original primary key and the other one to enforce its relationship with Tabe A. Then I want Table B to have a many-to-many relationship with Table X. As I'm adding the relationship, MySQL Workbench added Table Y with both of Table B primary keys and one primary key in Table X. So Table Y now has three primary keys.

It seems like the second primary key from Table B in the junction table is unnecessary since I can identify Table B with the original primary key. So do I still need the extra primary key? Or perhaps I should not have an identifying relationship between Table A and B?

Table A and B have a relationship something like User has many Post. Post must belong to a User. But Post already has a primary key of its own, so does the foreign key to User need to be a primary key?

EDIT

Here's the scenario (diagram link below). The tables I'm focusing on are snippet, snippet_topic and tag. From what I know, since every snippet must belong to a snippet_topic, it has an identifying relationship. So I used the identifying relationship in MySQL Workbench and they added snippet_topic ID as a primary key. Afterwhich I added a m:n relationship for tag and snippet. MySQL Workbench added snippet_topic ID to the junction table (but I removed it). Is there anything wrong with my design? Or is there a more correct way to this?

Legend: Yellow icon - primary key Red icon - not null Workbench EER Diagram


Solution

  • each table should only have one primary key which is only about this table. If you then want a second column in Table A containing the values of the table B primary key thats find. Just set up a second index to get performance if requires