mardi 25 août 2015

Correct MySQL structure for storing user-based data

So I have a question, I'm hoping it isn't too subjective.

I have a blog-style website, so on the homepage articles are loaded alongside the date published, the user that posted it, etc. Basic data like this.

I store it in MySQL like so:

article_id     username      date                     content          etc.
1              user1         2015-05-14 01:35:14      my content a  
2              user2         2015-05-16 02:33:15      my content b

This way, I can display it using one query, where I retrieve the username, date, content, etc.

My question is. I want to allow users the option to change their username. There are two options I see for this.

Either I continue storing data as I do now, and manually update tables like this with user-related data to the new username. Or I store data by a user_id rather than username, and have an extra query for each article loaded to get the associated username from another user table.

Which is the correct approach?

I ask this because I assume there's a recommended practice for this situation? Is it normal to store data by username and update it, or to store by id to avoid having to do this - but at the cost of the overhead when querying data. I'm guessing it's possible to display the username for id-based data in just one query, but that would still take significantly longer?

Aucun commentaire:

Enregistrer un commentaire