如何构建用户/组/成就数据库？ [英] How to build a user/group/achievement database?

问题描述

我正在开发一个新的网络平台，用户，组和用户可以获得成就（每个组都有自己的成就）来完成特定的任务，这些是我目前的表：

I'm working on a new web platform that has users, groups and those users can get achievements (each group has its own achievements) for doing specific tasks and these are the tables i have at the moment:

• 使用者


• 群组


• Group_users 用户的
  组，因为用户可以加入多个组）


• 成就


• User_achievements 存储用户的成就，因为用户可以有多个成就）

• Users
• Groups
• Group_users (used to store user's groups, since users can join more than one group)
• Achievements
• User_achievements (used to store user's achievements, since users can have more than one achievement)

我的问题是，在User_achievements ，我应该存储用户和组ID还是只保存组和用户之间的关系的group_user ID？

My question is, in the "User_achievements" table, should i store the user and group id's or only the group_user id that holds the relation between group and user?

对不起，如果问题不清楚。如果你有更好的方法这样做或任何提示，请告诉我，所以我可以做。

I'm sorry if the question is not clear. Also if you have a better way of doing this or any tip, please tell me so i can do it.

提前感谢！

推荐答案

首先，为了回答你的问题，我会将user_id作为外键存储在user_achievements表中，因为成就链接到用户而不是组

First off to answer your question, I would store just the user_id as a foreign key in the user_achievements table as the achievements are linked to the user not the group in this table.

如果成就也链接到组，如您在对另一个答案的评论中提到的，该关系应显示在成绩表中，而不是user_achievements表。

If the achievements are also linked to the group, as you mentioned in a comment to another answer, that relationship should be shown in the achievements table, not the user_achievements table.

为什么Group_users表有自己的唯一id（你似乎暗示它是），它不只是一种方式加入多对多的关系？如果这是case为什么不只是使group_users表的主键组合的user_id外键和groups_id外键。因为两个外键的组合本身是唯一的，所以不需要表的唯一ID。

Also why would the Group_users table have its own unique id (which you seem to be implying that it is), is it not just a way to join a many to many relationship? If this is the case why not just make the primary key of the group_users table a combination of the user_id foreign key and the groups_id foreign key. There is no need for a unique id for the table, as the combination of the two foreign keys is unique in itself.

编辑：

所以我这样做：

Users table:
    id (Primary Key)
    ... What ever other data you want to store for the user ...

Groups:
    id (Primary Key)
    ... What ever other data you want to store for the group ...

Group_users:
    user_id (Foreign Key to users.id) (Part of primary key)
    group_id (Foreign Key to groups.id) (Part of primary key)

Achievements:
    id (Primary Key)
    group_id (Foreign Key to groups.id)
    ... What ever other data you want to store for the achievement ...

User_achievements:
    achievement_id (Foreign key to achievements.id) (Part of primary key)
    user_id (Foreign key to users.id) (Part of Primary Key)

这篇关于如何构建用户/组/成就数据库？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！