CODEWITHSUNDEEP
databasepostgresqldatabase-designdata-modeling
Julian Betancourt
Julian Betancourt

Reputation: 357

Can a Database column have duplicate foreign keys?

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

Upvotes: 0

Views: 346

Answers (1)

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

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    |

Upvotes: 1

Related Questions