What would be the best approach for this model? (SQL)

Hello, I’m working on a project in which I intend to give awards to users (like medals). However, I wonder what would be the ideal structure for it.

The user model would have many medals, and each medal could belong to multiple users.

Any ideas?


What technology were you planning on using for this database? Since you mentioned SQL I assume you’re using a relational database like MySQL and could therefore use patterns like foreign keys.

With MySQL, the model is easy.

  |-- medalID
  |--etc, etc....

  |-- userID
  |--medalID (foreign key from MedalTable)
1 Like

I’m using PostgreSQL, along with an ORM that allows me to do stuff like has_many :medals, or belongs_to :user, stuff like that.

I’ll take a look into your suggestion, thank you!

From what I understand, it sounds like you will need a many-to-many relationship between the medal and user tables. In my experience, I typically handle this with lookup table that links to both the user table and medal table with foreign key constraints.

User Table
|-- UserID
|-- Username
|-- EmailAddress
|-- OtherUserProperty

Medal Table
|-- MedalID
|-- MedalName
|-- OtherMedalProperty

|-- KeyField
|-- UserID (Foreign Key to User Table)
|-- MedalID (Foreign Key to Medal Table)
|-- CreatedDate
* If medal can only be acquired once per user, make UserID and MedalID combined unique fields


This seems to be the best approach, thank you!