CODEWITHSUNDEEP
postgresqldatabase-design
Codeya
Codeya

Reputation: 11

Which method of storing USERS, ROLES & TEAMS in my relational DB is most efficient

I'm working on developing an app as part of my college assignment. It's a project management app, and I'm having trouble deciding the best way to store users and teams in my Postgres DB. Basically, users can signup and create/join teams. A user can be a part of multiple teams (each working on multiple projects). Users also have roles in teams (with varying permissions according to the role) and while they have only one role in a given team, they may have a different role in another one. In addition, users can mark some of their teams as favorites for easy access through the front-end.

I've come up with 3 ERDs to solve this.

First, store all users in one table and and all teams in another. Users table has all the data pertaining to a user, while the team table has the team data along with the members,roles and whether or not a user has marked this team as a favorite - like below.

ERD1

This will have a lot of data duplication - if a team has a hundred members, there will be 100 entries where teamid, name, description are the same.

So, in v2 I separated them and added a members table. Now, each team is saved once, and so is each user. A reference to the team and user is made each time a user joins/creates a team and is stored in the members table along with the user's role and whether or not they have favorited the team.

ERD2

But, I thought it might be bad to save roles as a string. If roles ever need to be changed/updated or I need to add new roles/rename roles, it would be easier with an ID rather than a string (I think).

So, then I came up with this.

ERD3

Now all roles, users and teams are stored once (its possible that I've made the roles table into something like a lookup table, which I've heard is a bad practice). All these can be referenced in the members table.

My DBMS concepts are a little weak though I have tried my best to follow steps to normalize it and bring it into BCNF form. But I'm still unsure if I've done this right, or what to fix if something is wrong.

So essentially, I would like to know:

  1. Is my table structure correct or incorrect?
  2. Should everything be split into multiple tables, or is some data duplication okay (since I can use multiple or creative queries to get whatever I need)?

Upvotes: 0

Views: 2804

Answers (1)

Brenda J. Butler
Brenda J. Butler

Reputation: 1485

I like your ERD3 best. I don't think it is overkill, I think it looks fine. Having a "members" table be mostly foreign keys into other tables is a common thing.

It is not necessary to eliminate every trace of commonality in every table - sometimes it is more efficient to put up with a small amount of duplication - but in your example I think your ERD3 looks good.

Upvotes: 1

Related Questions