-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathinit.sql
More file actions
102 lines (90 loc) · 4.23 KB
/
Copy pathinit.sql
File metadata and controls
102 lines (90 loc) · 4.23 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
-- Create database (if not exists)
CREATE DATABASE IF NOT EXISTS split_data;
USE split_data;
-- Table: group
CREATE TABLE IF NOT EXISTS `group` (
id INT AUTO_INCREMENT PRIMARY KEY,
group_name VARCHAR(255) NOT NULL,
description TEXT,
user_id INT NOT NULL COMMENT 'User ID who created the group',
created_dt DATETIME DEFAULT CURRENT_TIMESTAMP,
update_dt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
INDEX idx_user_id (user_id),
INDEX idx_created_dt (created_dt)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- Table: member
CREATE TABLE IF NOT EXISTS member (
id INT AUTO_INCREMENT PRIMARY KEY,
group_id INT NOT NULL,
member_id INT NOT NULL COMMENT 'User ID of the member',
FOREIGN KEY (group_id) REFERENCES `group`(id) ON DELETE CASCADE,
INDEX idx_group_id (group_id),
INDEX idx_member_id (member_id),
UNIQUE KEY unique_group_member (group_id, member_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- Table: expense
CREATE TABLE IF NOT EXISTS expense (
id INT AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(255) NOT NULL COMMENT 'Where or what user paid for (e.g., McDonald, Uber)',
tag VARCHAR(100) COMMENT 'Classification of expense type (e.g., food, travel)',
description TEXT COMMENT 'Comments/notes from user, can be null',
is_settled BOOLEAN DEFAULT FALSE COMMENT 'Whether all splits for this expense are paid',
group_id INT NOT NULL,
user_id INT NOT NULL COMMENT 'User ID who paid for the expense',
created_dt DATETIME DEFAULT CURRENT_TIMESTAMP,
updated_dt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (group_id) REFERENCES `group`(id) ON DELETE CASCADE,
INDEX idx_group_id (group_id),
INDEX idx_user_id (user_id),
INDEX idx_is_settled (is_settled),
INDEX idx_tag (tag)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- Table: split
CREATE TABLE IF NOT EXISTS split (
id INT AUTO_INCREMENT PRIMARY KEY,
expense_id INT NOT NULL,
group_id INT NOT NULL,
user_id INT NOT NULL COMMENT 'User ID involved in this split',
amount DECIMAL(10, 2) NOT NULL COMMENT 'Positive = paid, Negative = owe',
paid_or_not BOOLEAN DEFAULT NULL COMMENT 'NULL for payer, FALSE for unpaid debt, TRUE for paid debt',
created_dt DATETIME DEFAULT CURRENT_TIMESTAMP,
updated_dt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (expense_id) REFERENCES expense(id) ON DELETE CASCADE,
FOREIGN KEY (group_id) REFERENCES `group`(id) ON DELETE CASCADE,
INDEX idx_expense_id (expense_id),
INDEX idx_group_id (group_id),
INDEX idx_user_id (user_id),
INDEX idx_paid_or_not (paid_or_not)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- Insert sample data based on the provided examples
-- Group 1: food group
INSERT INTO `group` (id, group_name, description, user_id) VALUES
(1, 'food', 'Esther, Jordan, Kartik', 2);
-- Group 2: trip group
INSERT INTO `group` (id, group_name, description, user_id) VALUES
(2, 'trip', 'Finan, Jordan', 4);
-- Members for group 1
INSERT INTO member (id, group_id, member_id) VALUES
(1, 1, 1),
(2, 1, 2),
(3, 1, 3);
-- Members for group 2
INSERT INTO member (id, group_id, member_id) VALUES
(4, 2, 2),
(5, 2, 4);
-- Expenses
INSERT INTO expense (id, title, tag, description, is_settled, group_id, user_id) VALUES
(1, 'McDonald', 'food', '', FALSE, 1, 2),
(2, 'Uber ride', 'travel', '', TRUE, 2, 4);
-- Splits for expense 1
-- user_id=2 paid 100 (positive), user_id=1 and user_id=3 each owe 50 (negative)
INSERT INTO split (id, expense_id, group_id, user_id, amount, paid_or_not) VALUES
(1, 1, 1, 2, 100.00, NULL), -- user_id=2 paid, so paid_or_not is NULL
(2, 1, 1, 1, -50.00, TRUE), -- user_id=1 owes 50, has paid
(3, 1, 1, 3, -50.00, FALSE); -- user_id=3 owes 50, hasn't paid yet
-- Splits for expense 2
-- user_id=4 paid 30 (positive), user_id=2 and user_id=4 each owe 15 (negative)
INSERT INTO split (id, expense_id, group_id, user_id, amount, paid_or_not) VALUES
(4, 2, 2, 2, -15.00, TRUE), -- user_id=2 owes 15, has paid
(5, 2, 2, 4, 30.00, NULL), -- user_id=4 paid 30, so paid_or_not is NULL
(6, 2, 2, 4, -15.00, TRUE); -- user_id=4 also owes 15 (their share), has paid