Improves backend schema

2 files changed, 7 insertions, 2 deletions

diff --git a/backend/migrations/2024-08-31-084439_initial_setup/down.sql b/backend/migrations/2024-08-31-084439_initial_setup/down.sql
index 5e68c99..8cf4b1e 100644
--- a/backend/migrations/2024-08-31-084439_initial_setup/down.sql
+++ b/backend/migrations/2024-08-31-084439_initial_setup/down.sql
@@ -1,6 +1,6 @@
 DROP TABLE accounts;
 DROP TABLE budget_updates;
 DROP TABLE categories;
-DROP TABLE category_transactions;
+DROP TABLE transaction_categorisations;
 DROP TABLE category_transfers;
 DROP TABLE transactions;
diff --git a/backend/migrations/2024-08-31-084439_initial_setup/up.sql b/backend/migrations/2024-08-31-084439_initial_setup/up.sql
index f2461a5..a43f6dd 100644
--- a/backend/migrations/2024-08-31-084439_initial_setup/up.sql
+++ b/backend/migrations/2024-08-31-084439_initial_setup/up.sql
@@ -26,11 +26,16 @@ CREATE TABLE categories(
     initial_period INTEGER NOT NULL
 );
 
-CREATE TABLE category_transactions(
+CREATE TABLE transaction_categorisations(
     id INTEGER NOT NULL PRIMARY KEY,
     description TEXT NOT NULL,
     quantity INTEGER NOT NULL,
+    transaction_id INTEGER NOT NULL,
     category_id INTEGER NOT NULL,
+    FOREIGN KEY (transaction_id)
+        REFERENCES transactions (id)
+        ON UPDATE CASCADE
+        ON DELETE RESTRICT,
     FOREIGN KEY (category_id)
         REFERENCES categories (id)
         ON UPDATE CASCADE