在mysql中存储数据的最佳方法？

I have a pretty basic question on which is the preferred way of storing data in my database.

我有一个非常基本的问题,哪个是在我的数据库中存储数据的首选方法。

I have a table called "users" with each user getting a username and user_id. Now, I want to make a table called "comments" for users to comment on news.

我有一个名为“users”的表,每个用户都获得了用户名和user_id。现在,我想创建一个名为“评论”的表,供用户评论新闻。

Is it better to have a column in comments called "username" and storing the logged in user's name, or have a column called "user_id". If I use user_id I would have to make my sql statement have another select statement. "(SELECT username FROM users WHERE users.id = comments.user_id) as username". It seems like performance would be better just storing the username.

在注释中有一个名为“username”的列并存储登录用户的名称,或者有一个名为“user_id”的列是否更好?如果我使用user_id,我将不得不让我的sql语句有另一个select语句。 “(SELECT username FROM users WHERE users.id = comments.user_id)as username”。似乎只需存储用户名,性能会更好。

I thought I read to avoid duplicate data in a database though.

我以为我读过以避免数据库中的重复数据。

Which is better?

哪个更好?

Thanks

8 个解决方案

#1

Typically, you use ID fields to link tables together. The reason being (in your situation) that you might allow the person to change their username, but you don't want to try and update all the places that is at...

通常,您使用ID字段将表链接在一起。原因(在您的情况下)您可能允许此人更改其用户名,但您不想尝试更新所有位置...

Therefore, put the user_id in your comments table and pull the username out on a join, as you've shown.

因此,如您所示,将user_id放在评论表中并在联接上拉出用户名。

#2

If the user_id is the primary key then you should use user_id instead of username, if you want to use username instead of user_id then why do you have a user_id in the first place?

如果user_id是主键,那么你应该使用user_id而不是username,如果你想使用username而不是user_id那么为什么你首先要有user_id?

#3

If there's the potential of creating a large enough database, store the user_id in the comments table. Less overhead. Also consider that usernames my be modified easier this way.

如果有可能创建足够大的数据库,请将user_id存储在comments表中。减少开销。还要考虑通过这种方式更容易修改用户名。

#4

Data should be stored in (at least) third normalized form, so you should use the user_id as the primary key in the users table, and as a foreign key in the comments table and use this to get the details:

数据应该以(至少)第三种规范化形式存储,因此您应该使用user_id作为users表中的主键,并将其作为注释表中的外键使用以获取详细信息:

SELECT comments.*, users.username  
FROM comments, users
WHERE users.user_id = comments.user_id;

If you are getting the comments based on an article, you could do this like this:

如果您根据文章获得评论,可以这样做:

SELECT comments.*, users.username  
FROM comments, users
WHERE users.user_id = comments.user_id  
AND comments.article_id = '$current_article_id';

#5

Storing the userid (integer) will mean faster JOINs later. Unless you plan on having people dig through the database by hand, there's really no reason to use the username

存储用户ID(整数)意味着以后加快JOIN。除非你打算让人们手动挖掘数据库,否则没有理由使用用户名

#6

I'm pretty sure storing the user id in the comments table is sufficient. If you're returning rows from the comments table, just use the JOIN statement.

我非常确定在评论表中存储用户ID就足够了。如果要从comments表返回行,只需使用JOIN语句即可。

Cheers

#7

Which is going to be a unique identifier? The user_id, I'd bet, or you can't have two "John Smith"s in your system.

哪个是唯一的标识符? user_id,我打赌,或者你的系统中不能有两个“John Smith”。

And if volume is much of a concern, text matching the username field is going to be more expensive than linking to the users table in your query in the long term.

如果卷是一个值得关注的问题,那么匹配用户名字段的文本将比在长期内链接到查询中的用户表更昂贵。

#8

Numeric values are cheaper to join and index than an alphanumeric id. Use a number to uniquely identify a row. Another benefit is that the PK doesn't need to change if they need to change the user id. The last benefit is that this is the design of most modern web frameworks such as django and rails.

加入和索引的数字值比字母数字id更便宜。使用数字唯一标识行。另一个好处是,如果他们需要更改用户ID,则不需要更改PK。最后一个好处是,这是大多数现代Web框架的设计,例如django和rails。

#1