0

I need to create a Many to Many relationship and understand the need for an intermediate table to act as the link between the two. The understanding I have is the Group of ID's in the intermediate table should be unique to not duplicate data; is this correct?

The DB that I am designing is for a deck of cards.

I have two tables: decks and cards. The intermediary table is deck_cards.

The issue I am facing is: a deck can contain many cards but can also contain more than one of the same card.

Below is a screen shot of my current Schema. I am currently storing an additional bit of data in the column amount in the deck_cards table for a given relationship. Is this the appropriate way to approach this situation? Or, is there another intermediary table that is needed to be created?

many to many schema

Improve this question
3
  • A primary key implies it is unique. An joining table doesn't necessary have to have a primary key, it could just be a table that doesn't have a unique element. If there is a unique element (including composite elements), by all means use that as the PK.
    – danblack
    Commented Jul 26, 2020 at 2:00
  • So if I am understanding you correctly, my intermediary table can just have the two columns be FK (and not PK) and this will then allow me to have multiple of the same row? Is this the right convention in this situation (repeating the same row), or is it correct to have the columns be PK FK with the additional "amount" column representing how many of those cards are in a given deck in the intermediary table.
    – Michael B.
    Commented Jul 26, 2020 at 2:16
  • is how many a derived value? if so it shouldn't be in the table. Show sample data. PK(deck_id,card_id) means the pair of values is unique, not each is unique individually.
    – danblack
    Commented Jul 26, 2020 at 2:20

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.