Dmitry Dmitry - 4 months ago 8
SQL Question

sqlite - how to let registered users own collections?

I want to allow users registered to my database to have their own tables for various collections that are owned uniquely by their id.

The users are stored in:

(users :: id -> [id, email])


create table users (
id integer primary key autoincrement not null unique,
email varchar[255] not null
);


and they have a profile table:

create table profiles (
id integer unique primary key not null,
foreign key(id) references users(id)
);


I want to be able to add various collections that the user profile could reference, such as friends list, favorite music names, and list of previously used password hashes.

My initial thinking was to create a unique table for each of these collections and relate a table-name field in profiles to the actual name of the table, but this idea seems really silly since it floods the database with many nonsensical named tables.

I also have a concern that even though I can create a table for each collection, I have no idea how to reference a table named by a field in another table in SQL, so I would need to perform two queries and process the first before passing it on to second.


  1. get the collection id from the profiles table.

  2. access a table named the result of query 1.



Which feels wrong as well.

Is there another approach for mapping primary key to a collection-of-things than creating a table? And if not, should I be using different databases such as "friend_lists.db" for storing all of these tables?

Thanks ahead of time.

Answer

You are correct in thinking that dynamically creating tables is "weird". I've seen many people on Stack Overflow try to use this anti-pattern.

Whatever you were about to parameterize your table names with, move to a column in a single table.

For example, if you were thinking about using this schema:

CREATE TABLE user_1234_music(track_name TEXT);
CREATE TABLE user_5678_music(track_name TEXT);
CREATE TABLE user_5483_music(track_name TEXT);
-- and so on

Move the user ID to a column, with a foreign key constraint to ensure it points to a valid user.

CREATE TABLE friends(user_id INTEGER REFERENCES users(id), track_name TEXT);

This has several benefits:

  • No popular SQL database engine supports using query parameters in table names, meaning you would have to use string manipulation to "pass" in the value to the table name, exposing you to SQL injection vulnerabilities.
  • Most database do not have a way to dynamically select between different table names based on query results. Columns are easy to filter, either in WHERE or JOIN clauses.
  • Schema changes (adding/removing columns, indexes, constraints, etc) only have to happen on one table.
  • Databases are built to scale to millions of rows. They are not as well equipped to scale with large amounts of tables (or columns for that matter).