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:
Table user:
- user_id (key)
- user_name
- user_image
- user_link
- user_provider
Table comments:
- comments_id (key)
- user_id
- page_id (I don’t know exactly what this column is, I assume it’s a foreign key?)
- comments_text
- comments_date
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!
