CODEWITHSUNDEEP
phpmysqldatabasedesign-patternsdatabase-design
user3420034
user3420034

Reputation:

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?

Upvotes: 0

Views: 66

Answers (5)

Leandro Hermes Neto
Leandro Hermes Neto

Reputation: 15

You can create a table for users, and use a foreign key on field username, specifying the behavior on updates. Is something like this:

alter table posts add constraint fk_post_user foreign key (username) references users (name) on update cascade;

In this way, when you update a row on table users, all user names on table posts will be updated too.

Upvotes: 0

DirtyBit
DirtyBit

Reputation: 16772

Make a separate table for users something like:

-------------------
user_id | user_name
-------------------

Where user_id should be PK.

And another table, lets say article should look like:

-----------------------------------------------
arcticle_id | date |  content |  etc. | user_id
-----------------------------------------------

Where article_id could be a PK and user_id would be the FK from users table, making a relationship which could be used in other tables as well.

Upvotes: 0

TheAlexLichter
TheAlexLichter

Reputation: 7289

Create a seperate table with all user-related information and alter your current table, so only content and article related stuff is included. That's what I'd suggest you

Upvotes: 0

Rahul
Rahul

Reputation: 77866

Depends. Do you see there is a 1:1 relationship with Article:User if yes, then storing in a single table will probably suffice but generally an user will publish multiple articles which will make it a 1:* relationship and in which case you should create a separate table for UserDetailsd and have user_id as FOREIGN KEY in Article table probably.

Upvotes: 1

dinomix
dinomix

Reputation: 976

You should create a users table, store user_id which would be incremental and a user_name. When showing the user name in your app, join to the users table and show the name from that table and it will always be current. This is the best practice if you wish to allow user name changes. Updating all usernames it the articles table is not recommended. This will also allow you to store other user related information such as email, join date, etc... without having to keep all that in the articles table.

Upvotes: 0

Related Questions