Store array of items in SQL table

Question

I know this has probably been asked a million times but I can't find anything definite for me. I'm making a website involving users who can build a list of items. I'm wondering what would be the best way for store their items in an SQL table?

I'm thinking will I need to make a seperate table for each user since there I can't see any way to store an array. I think this would be inefficient however.

Answer 1

Depending on what an "item" is, there seem to be two possible solutions:

1. a one-to-many relationship between users and items
2. a many-to-many relationship between users and items

If a single item (such as a "book") can be "assigned" to more than one user, it's 2). If each item is unique and can only belong to a single user it's 1).

one-to-many relationship

create table users
(
   user_id    integer primary key not null,
   username   varchar(100) not null
);

create table items
(
   item_id    integer primary key not null,
   user_id    integer not null references users(user_id),
   item_name  varchar(100) not null
);

many-to-many relationship:

create table users
(
   user_id    integer primary key not null,
   username   varchar(100) not null
);

create table items
(
   item_id    integer primary key not null,
   item_name  varchar(100) not null
);

create table user_items
(
    user_id   integer not null references users(user_id),
    item_id   integer not null references items(item_id)
);

Because of your extremely vague description, this is the best I can think of.

There is no need to use an array or something similar. It seems you are new to database modelling, so you should read up about normalisation. Each time you think about "arrays" you are probably thinking about "tables" (or relations).

Edit (just saw you mentioned MySQL):
the above SQL will not create a foreign key constraint in MySQL (even though it will run without an error) due to MySQL's stupid "I'm not telling you if I can't do something" attitude. You need to define the foreign keys separately.