用户条目的数据库设计（使用mysql） [英] Database design for user entries (using mysql)

问题描述

我让用户输入的主要数据片段是一个称为活动的对象，由几个文本字段，几个字符串等组成。文本字段之一称为描述可能相当长（如长篇博文）。

下面是我想到的一些解决方案：

$ b $为每个用户的活动（即activities_userX，X范围超过

有一个单独的mysql表）b

使用json将这些对象编码为字符串，并将它们作为列存储在主表中

为所有这些活动对象分别创建一个表，索引他们;则对于主表中的每个用户具有对应于哪些活动是他们的索引的列表。

这些方法的优缺点是什么？更重要的是，我还能做什么？

谢谢。对每个用户的活动有一个单独的mysql表，即activities_userX，X range over

解决方案

每个用户的表格？

使用json将这些对象编码为字符串，并将它们作为列存储在主表中

JSON是一种很好的传输语言。

为所有这些活动对象创建一个单独的表，并对它们建立索引;

更接近。

这种关系通常被称为有很多。在这种情况下，A用户有很多活动。

您应该有一张用户表和一张活动表。

活动表的其中一个列应该是指向用户表主键的外键。

然后您就可以：

 选择字段，i，想从活动WHERE userid =？ 
  

或

  SELECT users.foo，users.bar，activities.description from users，activities 
 WHERE user.userid = activities.userid 
  

The main pieces of data I'm having my users enter is an object called an "activity" which consists of a few text fields, a few strings, etc. One of the text fields, called a "Description", could possibly be quite long (such as a long blog post). For each user I would like to store all of their activity objects in a mysql database.

Here are some solutions I've thought of:

• Have a separate mysql table for each user's activities, i.e. activities_userX, X ranging over

• Use json to encode these objects into strings and store them as a column in the main table

• Have one separate table for all these activities objects, and just index them; then for each user in the main table have a list of indices corresponding to which activities are theirs.

What are the pros/cons of these methods? More importantly, what else could I be doing?

Thanks.

解决方案

Have a separate mysql table for each user's activities, i.e. activities_userX, X ranging over

A table for every user? That just means an insane number of tables.

Use json to encode these objects into strings and store them as a column in the main table

JSON is a good transport language. You have a database for storing your data, use its features.

Have one separate table for all these activities objects, and just index them; then for each user in the main table have a list of indices corresponding to which activities are theirs.

Getting closer.

This sort of relationship is usually known as 'has many'. In this case "A user has many activities".

You should have a table of users and a table of activities.

One of the columns of the activities table should be a foreign key that points to the primary key of the user table.

Then you will be able to do:

SELECT fields, i, want from activities WHERE userid=?

Or

SELECT users.foo, users.bar, activities.description from users,activities 
    WHERE user.userid=activities.userid

这篇关于用户条目的数据库设计（使用mysql）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！