DB/table design help [closed]

I have 50,000 unique cards in a table. A minimum of 2 cards and up to 20 cards can be chosen by the user and they can write a blurb about it. There will be times when the cards chosen by different users will overlap. When the exact same cards are chosen, I’d like to store the new blurb but keep the table clean as possible as many people may choose the same 2 cards but write different blurbs about the 2 cards. How should I design my table to store this information?

I’d suggest, you store the ‘chosen cards’ in an separate assignment-table and group this assignment per user.

For example your table ‘cards’:

 table: cards
--------------
 card_id | PK
 ...

The following table stores the blurb for the given user:

 table: blurbs
--------------------------------------------------------------
 blurb_id   | PK
 user_id    | (given user, that wrote the blurb)
 blurb_text | (whatever you want to store here, e.g. a blob?)

Last, but not least, you need the assignment-table, to define, which user/blurb is assigned to which cards:

 table: card_assignment
------------------------------------------------------------------------------------------
 blurb_id | PK
 card_id  | PK (the cards to be chosen - card can only be assigned once per 'user-blurb')

Now, you can select any information, you need across that three tables:

E.g.: Any cards chosen by a particular user:

SELECT * 
FROM card_assignment a 
LEFT JOIN cards c ON (a.card_id = c.card_id)
WHERE EXISTS (
    SELECT 1 
    FROM blurbs b
    WHERE b.user_id = :user_id
    AND b.blurb_id = a.blurb_id)

Indeed, the select statements can get very complex, if you have to use multiple tables that way, but in my opinion, this solution is as clean as possible.

Don’t forget to put referential constraints on the fields, if you use mySQL with Inno-DB!