Best way to store arrays of simple data in PostgresQL

Question

I am creating a database where one of the 'user' tables needs to have an array of foreign keys linking to multiple rows of another table. I'm trying to figure out the best way to handle this.

user	projects
jeff	[1,2,6]
dave	[3,4]

projects	otherstuff
1	'content 1'
2	'content 2'
3	'content 3'
4	'content 4'
5	'content 5'
6	'content 6'

I guess could make a table for every user called 'user_projects' but if I have 10k users, it seems frustrating to have 10k tables with redundant rows if each some users work on the same project.

Thoughts?

Having a separate `user_projects` table which just has rows that map from a `user` to a `project` is actually very common and typically preferred in SQL. Easier to user inner joins to find all users working on a single project, all projects for a single user, etc. — DemiPixel, Oct 07 '21 at 18:46
DemixPixel is right: use a properly normalized many-to-many relationship. While Postgres does offer arrays, you can't have foreign key constraints with them and in general there are more complicated to work with. — , Oct 07 '21 at 18:59
I see... I'm more familiar with NoSQL so this relational mapping definitely gives me pause. Found this article tho for any future human reading this that wants more info. This was really helpful. https://launchschool.com/books/sql_first_edition/read/multi_tables — wizardjuice, Oct 07 '21 at 20:51

score 0 · Accepted Answer · answered Oct 07 '21 at 19:04

0

With the many to many relationship. Here is the ERD diagram.

answered Oct 07 '21 at 19:04

dodododo97

165
1
10

Best way to store arrays of simple data in PostgresQL

1 Answers1