建模用户，组和成员资格数据库表 [英] Modeling User, Group and Membership database tables

问题描述

我想创建用户，组和成员表如下。 A用户可能属于一个或多个组并且一个组可能有一个或多个用户。用户和组表之间的许多一对多的关系，利用成员关系表维护。我想保持会员资格（批准/等待/东西，别的？）和会员审批信息，以及。成员审批者将是管理组中的用户之一。以下是一个粗略的方案，我在我的脑海里。我是新来的数据库建模，并希望得到下述实施一些反馈。尤其是我不知道的成员表中是否应该有approved_by列使用USER_IDFK或者我应该创建一个单独的表保持MembershipStatus（ID，membership_id，状态，approved_by）？我猜有一个单独的表MembershipStatus这里是比较合适的，但不知道这件事。另外，我打算使用ActiveRecord在此应用程序的ORM。我在执行这个设计/架构AP preciate任何意见或建议。

I am trying to create User, Group and Membership tables as follows. A User may belong to one or many groups and a Group may have one or many users. The many-to-many relationship between User and Group table is maintained using Membership table. I would like to maintain Membership status (approved/pending/something-else??) and Membership approver information as well. The membership approver will be one of the user in the admin group. Following is a rough schema that I have in my mind. I am new to database modeling and would like to get some feedback on following implementation. In particular I am not sure whether Membership table should have 'approved_by' column using 'user_id' FK or should I create a separate table for maintaining MembershipStatus(id, membership_id, status, approved_by)?? I am guessing having a separate table MembershipStatus is more appropriate here but not sure about it. Also, I am planning to use ActiveRecord as an ORM in this application. I appreciate any comments or suggestions in implementing this design/schema.

User:
  id, Primary Key
  userid, Integer, Unique
  username, String, Unique
  email, String, Unique
  created_at, DateTime
  updated_at, DateTime

Group:
  id, Primary Key
  groupid, Integer, Unique
  groupname, String, Unique
  created_at, DateTime
  updated_at, DateTime

Membership:
  PK(user_id, group_id)
  status, String
  approved_by, user_id FK to User table 
  created_at, DateTime
  updated_at, DateTime

MembershipStatus 
  id
  membership_id FK to Membership table
  status, String
  approved_by, user_id FK to User table 
  created_at, DateTime
  updated_at, DateTime

- 谢谢

-thanks

推荐答案

看起来pretty的好。

looks pretty good.

您展示approved_by 2表。如果保留MembershipStatus表，然后就是哪里是应该属于。还-the名'approved_by意味着批准的可能或可能不存在的状态。你可能会想到另外一个名字......

You show approved_by in 2 tables. If you keep the MembershipStatus table, then that is where is should belong. also -the name 'approved_by' implies the status of 'approved' which may or may not exist. you might think of another name...

您还应该有一个表可能是确定哪些用户是管理员对哪些群体。这样你可以code可高达数据库端的允许谁审批以及专卖店究竟是谁并批准的安全性。

you should also have a table probably that identified which Users are admins for which groups. that way you can code up database side security for who is allowed to approve as well as store who actually did approve.

另外，我不存储审核历史记录在表中你是显示一个大风扇。无论是使用内置的审计数据库，或拉了出来到另一个表来记录审计的历史。

also, I am not a big fan of storing audit history in the table as you are showing. Either use the database built in audit, or pull that out to another table to record audit history.

最后，ID和用户ID似乎是多余的。去与用户ID。 （在其它表相似）

finally, id, and userid seem redundant. go with userid. (similar on other tables)

这篇关于建模用户，组和成员资格数据库表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！