Last active
November 26, 2021 23:46
-
-
Save nmchenry01/b4d146631a377776bb4d1d3d060882fe to your computer and use it in GitHub Desktop.
A example of a PostgreSQL schema for multi-tenancy
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
CREATE TABLE tenant ( | |
id INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, | |
name CHARACTER VARYING NOT NULL UNIQUE, | |
created_at TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT NOW(), | |
last_modified_at TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT NOW() | |
); | |
CREATE TABLE product ( | |
id INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, | |
name CHARACTER VARYING NOT NULL, | |
description CHARACTER VARYING NOT NULL, | |
price NUMERIC(12, 2) NOT NULL, | |
tenant_id INTEGER NOT NULL REFERENCES tenant (id) ON DELETE CASCADE, | |
created_at TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT NOW(), | |
last_modified_at TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT NOW() | |
); | |
CREATE TABLE product_image ( | |
id INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, | |
image_url CHARACTER VARYING NOT NULL, | |
product_id INTEGER NOT NULL REFERENCES product (id) ON DELETE CASCADE, | |
created_at TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT NOW(), | |
last_modified_at TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT NOW() | |
); |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment