How to create and normalise this SQL relational database?

Question

I am trying to design an SQL lite database. I have made a quick design on paper of what I want.

So far, I have one table with the following columns:

• user_id (which is unique and the primary key of the table)
• number_of_items_allowed (can be any number and does not have to be unique)
• list_of_items (a list of any size as long as it is less than or equal to number_of_items_allowed and this list stores item IDs)

The column I am struggling the most with is list_of_items. I know that a relational database does not have a column which allows lists and you must create a second table with that information to normalise the database. I have looked at a few stack overflow answers including this one, which says that you can't have lists stored in a column, but I was not able to apply the accepted answer to my case.

I have thought about having a secondary table which would have a row for each item ID belonging to a user_id and the primary key in that case would have been the combination of the item ID and the user_id, however, I was not sure if that would be the ideal way of going about it.

Answer 1

Consider the following schema with 3 tables:

CREATE TABLE users (
  user_id INTEGER PRIMARY KEY, 
  user TEXT NOT NULL,
  number_of_items_allowed INTEGER NOT NULL CHECK(number_of_items_allowed >= 0)
);

CREATE TABLE items (
  item_id INTEGER PRIMARY KEY, 
  item TEXT NOT NULL
);

CREATE TABLE users_items (
  user_id INTEGER NOT NULL REFERENCES users(user_id) ON UPDATE CASCADE ON DELETE CASCADE, 
  item_id INTEGER NOT NULL REFERENCES items (item_id) ON UPDATE CASCADE ON DELETE CASCADE, 
  PRIMARY KEY(user_id, item_id)
);

For this schema, you need a BEFORE INSERT trigger on users_items which checks if a new item can be inserted for a user by comparing the user's number_of_items_allowed to the current number of items that the user has:

CREATE TRIGGER check_number_before_insert_users_items 
BEFORE INSERT ON users_items
BEGIN
   SELECT
     CASE
       WHEN (SELECT COUNT(*) FROM users_items WHERE user_id = NEW.user_id) >=
            (SELECT number_of_items_allowed FROM users WHERE user_id = NEW.user_id)
         THEN RAISE (ABORT, 'No more items allowed')
     END;
END;

You will need another trigger that will check when number_of_items_allowed is updated if the new value is less than the current number of the items of this user:

CREATE TRIGGER check_number_before_update_users 
BEFORE UPDATE ON users
BEGIN
   SELECT
     CASE
       WHEN (SELECT COUNT(*) FROM users_items WHERE user_id = NEW.user_id) > NEW.number_of_items_allowed
         THEN RAISE (ABORT, 'There are already more items for this user than the value inserted')
     END;
END;

See the demo.