I came from old school mysql and was surprised that postgresql can do array.
In old days, let's say you have User and Fruit tables. Each user can have multiple fruits. In order to store what fruit each user has, we need to create a third table to store user and fruit ids.
But seems like PostgreSQL can have a column say fruits in User that stores an array of Fruit.id.
My question is, which one is better? In terms of performance and possible future extension.
It depends what you want to do with the data. But, for the example you give, the junction table is the better approach under most circumstances. Say:
create table UserFruits (
UserFruitId serial primary key,
UserId int references users(UserId),
FruitId int references fruits(FruitId)
);
Why? Because you can explicitly declare foreign key relationships, and the database will validate the data when it is being input. In addition, you might want to include other information in the junction table, such as the date/time when the fruit was added or the price.
If you have other types of lists -- say not related to other tables, but just lists -- then using arrays can make a lot of sense. From a performance perspective, it depends what you want to do with the lists. But for many purposes, arrays are faster, if that is a concern.
