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

1

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?

Thanks!

2

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.

MedalTable
  |-- medalID
  |--medalName
  |--medalValue
  |--etc, etc....

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

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!

4

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
…

UserMedalTable
|-- 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

2 Likes
5

This seems to be the best approach, thank you!