0

I'm building an app for trading cards for a given game. This means, a user can have multiple cards and even repeated cards. This is may approach but I don't know if it's correct (or even possible):

Users

---------------------------
|id| name | cards_ids     |
---------------------------
|20| John | 31, 40, 50, 50|
---------------------------

Cards

-------------------------------
|id| name      | type         |
-------------------------------
|31| Monster31 |  Aqua Monster|
-------------------------------
|50| Monster50 |  Rock Monster|
-------------------------------
|40| Monster40 |  Air Monster |
-------------------------------

As you can see, a user can have many cards even if they are the same. Would this duplicate foreign keys approach work fine? I will do this using Postgres, if that's relevant

Julian Betancourt
  • 337
  • 2
  • 5
  • 11
  • Hi. This is a faq. Please always google many clear, concise & specific versions/phrasings of your question/problem/goal with & without your particular strings/names & read many answers. Add relevant keywords you discover to your searches. If you don't find an answer then post, using use one variant search for your title & keywords for your tags. See the downvote arrow mouseover text. PS [Is storing a delimited list in a database column really that bad?](https://stackoverflow.com/q/3653462/3404097) – philipxy Aug 28 '18 at 04:01
  • PS An SQL FK constraint says the subrow values for a list of columns appears elsewhere as a PK or UNIQUE NOT NULL value. You don't have that here. "duplicate foreign keys" is just a list of some words that individually have something to do with what is going on, but does not express it. If you want to communicate & reason clearly make the effort to use enough words, phrases & sentences to clearly say what you mean. – philipxy Aug 28 '18 at 05:41

1 Answers1

1

You need think third normal form when designing your database.

In this case you want add the number of cards as a property

Users

-----------
|id| name |
-----------
|20| John |
-----------

CardsOwned

--------------------------------
|user_id| card_type_id  | count |
--------------------------------
|20     | 31            |   1   |
|20     | 40            |   1   | 
|20     | 50            |   2   |
--------------------------------

Or even better they should have their own id. Even when two cards are the same monster, they can have different attributes like "Near Mint" or "Mint"

Your cards definition should be something like cards_type where you define the card. But the cards own by anyone are the cards where even when are the same cards they have different id because are two different cards

------------------------------------------
| card_id |  card_type_id  | condition   | 
------------------------------------------
|  1      |  31            |   Mint      |
|  2      |  40            |   Near Mint | 
|  3      |  50            |   Used      |
|  4      |  50            |   Mint      |
------------------------------------------

then you need the ownership table to control who own what

CardsOwned:

 | card_id  | owner_id |
 |    1     |    20    |
 |    2     |    20    |
 |    3     |    20    |
 |    4     |    20    |
Juan Carlos Oropeza
  • 47,252
  • 12
  • 78
  • 118