current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top
up vote 1 down vote favorite

I am creating a database schema for user management in PostgreSQL. As, it supports array, so I am trying with three approaches.

Which one is best, querying with joins or getting/setting role data with array?

Considering large user base in near future.

Approach 1:

User table

id 
user_name

Role table

id 
role_name

RoleUser table

role_id 
user_id

Approach 2

User table

id
user_name
[integer array of role id]

Role

id 
role_name

Approach 3

User table

id
user_name
[string array of role key]

Role

id 
role_name
role_key
share|improve this question
5  
The first. Only the first. – dezso Mar 16 at 14:06
    
But, in 2 & 3 approach, I can save join while authorizing user. So, isn't it good? – Amreesh Tyagi Mar 16 at 14:54
1  
Why do you want to save a join? Wait, you don't. Try to measure the performance difference of both approaches. Just add a bunch of fake users, and measure. – dezso Mar 16 at 15:23
2  
You are using a relational DBMS and you are thinking about saving a join? That makes no sense. Such products are expected to work with joins efficiently. That's part of their job. Your Approach 1 is the way to implement a many-to-many relationship in an RDBMS – if you want to go the relational road, that is. – Andriy M Mar 16 at 15:59
3  
The basic storage is cleanest in approach 1. Implementation basics: How to implement a many-to-many relationship in PostgreSQL? For special purposes or display, you can always add a (materialized) view, aggregating arrays or strings like in approach 2 or 3. – Erwin Brandstetter Mar 16 at 17:26

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.