As Ben has linked, those are good resources.
I personally like to give my unique keys unique names – something like user_id, or company_id, this way when creating joins, it’s very easy to see what connects to what.
I do personally take it a step further and make all columns have unique names to a certain extent, sort of ‘namespacing’ them in a way. This allows for joining multiple foreign keys, etc. a simple thing.
My table structure would like more like this:
- user_id (key)
- comments_id (key)
- page_id (I don’t know exactly what this column is, I assume it’s a foreign key?)
Here’s a join to show you all comments made by a user (assuming user_id = 1)
SELECT * FROM comments c JOIN user u ON u.user_id = c.user_id WHERE u.user_id = 1
Want to show all users that have made a comment?
SELECT * FROM user u RIGHT JOIN comments c ON u.user_id = c.user_id
Using the right join here will only show you the users that are also in the comments table.
These are just some simple examples, hope that helped!