what is difference between creating another table and using arrays in postgres

Question

In my university, I was given a task to create a simple university database with some tables like student, departments and etc. There was an interesting moment when I did relations between students and classes, one student may choose multiple classes, I was taught to create the third table with two FKs, and it should look like this Adam(id - 1) takes math course(id - 5) and in the third table, the record would be (1, 5), and here is a question why should we prefer third table instead of Arrays, for me it looks much easier to hold student's classes as an additional column in the student table. Here is an example, imagine student table (id, name, age, arrayOfClassesID) (1, Adam, 20, [1,8,9,6,7]).

P.S. This is not my homework, I have already done it, but it is really interesting for me

For one, you can't have a foreign key that ensures that only valid class IDs are stored in that array — user330315
– user330315, Commented Mar 5, 2019 at 17:03
@a_horse_with_no_name, yeah It is the first thing that came into my mind. But except this, what about performance, because by using arrays we remove JOIN and as far as I know JOIN costs a lot — Akezhan
– Akezhan, Commented Mar 5, 2019 at 17:06
No, JOINs do not "cost a lot" that is a common misconception. It's what relational database are built around. The only time where array kind of make sense is, when you always read and write all elements as a one value and never have to lookup single elements in the array. To quote the manual "Arrays are not sets; searching for specific array elements can be a sign of database misdesign" — user330315
– user330315, Commented Mar 5, 2019 at 17:12
@a_horse_with_no_name, oh great, that is a really good answer, so If I select all students and their classes, array makes sense, but If I will do something like select all students who take math and biology, in that case, an array is a bad practice. Am i right? — Akezhan
– Akezhan, Commented Mar 5, 2019 at 17:17
Using array for FKs would lead to performance problems in terms of linear search inside array. Moreover, it makes difficult to query — zeitnot
– zeitnot, Commented Mar 5, 2019 at 17:18

Laurenz Albe · Accepted Answer · 2019-03-05 17:17:59Z

9

From a theoretical point of view, a problem is that such a design violates the first normal form. This has practical repercussions:

As a_horse_without_name commented, the number one problem is that you cannot have foreign key constraints on array elements.
Indexing for fast searches only works in a limited fashion using GIN indexes and the @> operator. Searches with LIKE or > cannot be optimized.
Searching all students for a class will be less efficient, even with a GIN index, and the query will be more complicated and less intuitive.
If you want to remove a class for the student, you'll have to rewrite the whole array. This is maybe no big problem here, but with longer arrays it can hurt.

Using a mapping table is just the natural way to do this in a relational database.

answered Mar 5, 2019 at 17:17

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

what is difference between creating another table and using arrays in postgres

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related