Changes between Initial Version and Version 1 of ddlScript.sql

Timestamp:

12/08/24 22:18:59 (5 weeks ago)

Author:

211101

Comment:

--

ddlScript.sql

@@ +1 @@
+
+{{{#!sql
+-- Delete tables if they exist
+DROP TABLE IF EXISTS transaction_breakdown CASCADE;
+DROP TABLE IF EXISTS transaction CASCADE;
+DROP TABLE IF EXISTS transaction_account CASCADE;
+DROP TABLE IF EXISTS tag CASCADE;
+DROP TABLE IF EXISTS "user" CASCADE;
+
+-- Create USER table
+CREATE TABLE "user" (
+    user_id SERIAL PRIMARY KEY,
+    user_name VARCHAR(50) NOT NULL,
+    email VARCHAR(100) NOT NULL,
+    password VARCHAR(100) NOT NULL
+);
+
+-- Create TRANSACTION_ACCOUNT table
+CREATE TABLE transaction_account (
+    transaction_account_id SERIAL PRIMARY KEY,
+    account_name VARCHAR(50),
+    balance DECIMAL(10, 2),
+    user_id INT REFERENCES "user"(user_id)
+);
+
+-- Create TAG table
+CREATE TABLE tag (
+    tag_id SERIAL PRIMARY KEY,
+    tag_name VARCHAR(50) NOT NULL
+);
+
+-- Create TRANSACTION table
+CREATE TABLE transaction (
+    transaction_id SERIAL PRIMARY KEY,
+    transaction_name VARCHAR(100),
+    amount DECIMAL(10, 2) NOT NULL,
+    net_amount DECIMAL(10, 2),
+    date TIMESTAMPTZ,
+    tag_id INT REFERENCES tag(tag_id)
+);
+
+-- Create TRANSACTION_BREAKDOWN table
+CREATE TABLE transaction_breakdown (
+    transaction_breakdown_id SERIAL PRIMARY KEY,
+    transaction_id INT REFERENCES transaction(transaction_id),
+    transaction_account_id INT REFERENCES transaction_account(transaction_account_id),
+    spent_amount DECIMAL(10, 2),
+    earned_amount DECIMAL(10, 2)
+);
+}}}