2

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

user671556
  • 67
  • 2
  • 6
  • why ID and userID and ID and GroupID I don't see the reason to keep 2 sets of IDs in each table. you keep the ID field hidden and you expose the Name fields to the users.. – xQbert Nov 15 '11 at 23:16
  • What happens if users leave, do you want to retain history of memberships or the fact they were once members? I think I would keep membership as is with a FK to user and name membershipstatus to MembershipJRNL to keep track of history. I would also consider a table with all the different types of status allowed for membership . Lastly what about a "Deleted_at" to show the record is no longer active if not journalizing the security. – xQbert Nov 15 '11 at 23:21
  • The userID and groupID are related to Linux system's user and group database and hence I kept it different from ID (primary key) in database tables which are usually incremented serially. The system user/group IDs may not have the same convention and hence I kept them distinct. Thanks for the suggestion on auditing these accounts. I hadn't thought about it. I will consider your and Randy's suggestions and re-post my schema later again. Thank you for your comments. – user671556 Nov 16 '11 at 00:26

1 Answers1

2

looks pretty good.

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...

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.

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

Randy
  • 16,480
  • 1
  • 37
  • 55
  • Thanks for the reply Randy. I wasn't sure about adding a separate MembershipStatus table and that's what I was confused about - whether to have separate MembershipStatus table for maintaining status and approved_by OR store it in the Membership table only. If I am using MembershipStatus table then I won't be adding status and approved_by columns to the Membership table. Also, thanks for the suggestion about having GroupAdmin type table. I thought about right after posting the question. :) – user671556 Nov 16 '11 at 00:15