I'm creating a database for the first time! The goal is to have a database for users. I'm using PHPMyAdmin on my localhost (XAMPP) to set it up.
This is pretty straightforward for things like "username" and "password", but one of the fields would be a list of activities that the user participates in.
So for instance, "Activities: Skiing, Rowing, Swimming" would be what this would look like on their page.
But I am not sure how to set this up on PHPMyAdmin. I can't just create a field called "Activities" of type VARCHAR(255) because I think it would store their interests in one box, which isn't good because I need to have these separatedly sorted for later access (For instance, I'd need to query all the users that have the "Swimming" activity in common).
What approach should I ideally be using?
Thank you!
Table users
---------------------------------------
| id | username | password |
---------------------------------------
| 1 | john | somehashedpass1 |
| 2 | carl | somehashedpass2 |
| 3 | maria | somehashedpass3 |
---------------------------------------
Table activities
--------------------------------------
| id | user_id | activity |
--------------------------------------
| 1 | 1 | Skiing |
| 2 | 1 | Rowing |
| 3 | 1 | Swimming |
| 4 | 2 | Skiing |
| 5 | 2 | Rowing |
| 6 | 3 | Skiing |
--------------------------------------
Then use JOIN or Sub-queries to fetch the required data.
Also, you could implement a 3-tables schema to define users, activities and relationships, like this:
Table users (no variation)
---------------------------------------
| id | username | password |
---------------------------------------
| 1 | john | somehashedpass1 |
| 2 | carl | somehashedpass2 |
| 3 | maria | somehashedpass3 |
---------------------------------------
Table activities
-----------------
| id | activity |
-----------------
| 1 | Skiing |
| 2 | Rowing |
| 3 | Swimming |
-----------------
Table relationships
------------------------------
| id | user_id | activity_id |
------------------------------
| 1 | 1 | 1 |
| 2 | 1 | 2 |
| 3 | 1 | 3 |
| 4 | 2 | 1 |
| 5 | 2 | 2 |
| 6 | 3 | 1 |
------------------------------
This will be basicaly the same as the previous implementation, but you'll have a better structured database.