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 not null
create table profiles (
id integer unique primary key not null,
foreign key(id) references users(id)
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: