What is an effective way of labeling columns in a database? I used to label columns in my databases like this:

user_id
user_name
user_password_hash


To avoid conflicts when joining two tables, but then I learnt some more on how to alias tables, and I stopped doing this. 

What is an effective way of labeling columns in a database? Why?
In your case, the prefix user is redundant. We (the devs in charge) know that this is the table user, so why add user_ prefix in front of every field?

What I would suggest to you is to do it with a more natural approach.

What are the characteristics of a Person: Last Name, First Name, Birthdate, Nationality, etc...

What are the characteristics of a Car: Model, Year, Color, Energy, etc...

Your column should be named as natural as possible, it would make the schema more clear for everybody, for you and the ones coming after you. This is also called the Maintenance phase, and anything you can do to make the maintenance easier is usually worth the effort.