Avoid Joins: Creating Tables and Inserting Data

create_tables.sql

-- User Table
CREATE TABLE user (
id serial PRIMARY KEY,
account_id int not NULL,
name varchar(10)
);

-- Purchase Table
CREATE TABLE purchase (
id SERIAL PRIMARY KEY,
data text not NULL,
user_id int not NULL
);

-- Populate our tables (might not be the most efficient way)
INSERT INTO USER (account_id)
SELECT generate_series(1,50000000) AS random_id;

INSERT INTO purchase (data, user_id)
SELECT 'Cereals' AS data,
       generate_series(1,50000000);
       
INSERT INTO purchase (data, user_id)
SELECT 'Milk' AS data,
       generate_series(1,50000000);


-- To mock a more real world example, we will add necessary indices
CREATE index ON "user" ("account_id");
CREATE index ON "purchase" ("user_id");