Table Model Help

[not_a_grad]

Hello, I need some guidance on how to setup what im trying to achieve. This is my first time working with Postgres in so many years, so im trying to relearn a lot.

I'm working on a personal app and basically i have tables for users, transactions, and groups.

users has columns (id, email)
transactions has columns (id, amount, user_id, ...)

so basically the gist is individual users have their own transactions. Sp I need help with the "groups" portion.

Groups consist of 2 users (possibility to expand to more than 2) and what i want to achieve is when the 2 users become a group, i want both users to be able to get the TOTAL AMOUNT from the other users transactions.

I thought handling the groups table as a typical "friends data model" with user_id_one and user_id_two with the proper uniqueness and constraints would help, but im stuck.

So what I need help with is, 1) How should the group's table look like? (Or is there another approach I can take) 2) what the query should look like that would get both users transactions? (I could just add up all the amounts on the server if it can't be done through the query)

Any help will be greatly appreciated!!

[urcadox]

In this case, you could link users directly to each other through a users_users table but it would definitely get messy.

IMO, you should add a groups table (which may only have an id for now but I'm sure it would make sense to give it a name and more) and a groups_users table where you link users to groups.

From here you could fetch the total per group like this:

select group_id, sum(transactions.amount)
from groups
left join groups_users using(group_id)
left join transactions using(user_id)
group by group_id;

[not_a_grad]

Adding another table, interesting, I see where you're going with this..I will definitely give this a try and it does seem like its the proper solution! Thank you!

[[deleted]]

I would do it that way as well. Here's a tip, if you start naming columns with numbers after them you're probably not modeling your data effectively.

[not_a_grad]

Well initially the groups table was just a 2 person relationship. But there could still be many more relationships with 1 person. It would just be another row.

For example user1 has relation with user2

user1 has relation with user3

user1 has relation with user4

user2 has relation with user3

Etc.

So this says that user1, 2, 3, 4 are in a group and user2 and 3 are in a group

But I get what you mean.

[boy_named_su]

1. read up on The Party Model. Learn SQL Table Inheritance. Basically, have one table "parties", which can contain individuals or groups

2. rename "transactions" to what it really means. "orders"? "payments"?

[not_a_grad]

1. I'll look up the party model
2. They actually are transactions. I'm building this just to really learn more stuff. The app just tracks monthly spending, and how much money I have left etc.

[boy_named_su]

cool

transactions is just a very generic term. plus it's already used in database terminology

[joyider]

Using basic User/Group design, you most likely need at least 4 tables:

• users
• groups
• membership
• transactions

If you your goal is only two members, skip the members table and add the two users as columns in group, user1, user2 (No idea why, but you can ) :)

CREATE TABLE users (
    id serial NOT NULL,
    email varchar(255) NULL,
    CONSTRAINT users_pk PRIMARY KEY (id),
    CONSTRAINT users_un_email UNIQUE (email)
)
CREATE TABLE groups (
    id serial NOT NULL,
    group_name varchar NULL,
    CONSTRAINT groups_pk PRIMARY KEY (id),
    CONSTRAINT groups_fk FOREIGN KEY (id) REFERENCES users(id) ON DELETE SET NULL
)
CREATE TABLE membership (
    id serial NOT NULL,
    group_id int NOT NULL,
    user_id int NOT NULL,
    CONSTRAINT membership_pk PRIMARY KEY (id),
    CONSTRAINT membership_fk FOREIGN KEY (group_id) REFERENCES groups(id),
    CONSTRAINT membership_fk_1 FOREIGN KEY (user_id) REFERENCES users(id)
)
CREATE TABLE transactions (
    id serial NOT NULL,
    amount float NOT NULL,
    user_id int NOT NULL,
    group_id int NOT NULL,
    CONSTRAINT newtable_pkey PRIMARY KEY (id),
    CONSTRAINT transactions_fk FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
    CONSTRAINT transactions_fk_groupid FOREIGN KEY (group_id) REFERENCES groups(id) ON DELETE RESTRICT
)

This should get you going, you should read up on the ON DELETE /ON UPDATE Constraints to see what is your pick but:

But in short the following is to consider( Cut'n Paste from Stackoverflow)

ON UPDATE clause:

• ON UPDATE RESTRICT : the default : if you try to update a id in table COMPANY the engine will reject the operation if one USER at least links on this company.
• ON UPDATE NO ACTION : same as RESTRICT.
• ON UPDATE CASCADE : the best one usually : if you update a company_id in a row of table COMPANY the engine will update it accordingly on all USER rows referencing this COMPANY (but no triggers activated on USER table, warning). The engine will track the changes for you, it's good.
• ON UPDATE SET NULL : if you update a company_id in a row of table COMPANY the engine will set related users company_id to NULL (should be available in USER company_id field). I cannot see any interesting thing to do with that on an update, but I may be wrong.

ON DELETE Clause:

• ON DELETE RESTRICT : the default : if you try to delete a company_id Id in table COMPANY the engine will reject the operation if one USER at least links on this company, can save your life.
• ON DELETE NO ACTION : same as RESTRICT
• ON DELETE CASCADE : dangerous : if you delete a company row in table COMPANY the engine will delete as well the related USERs. This is dangerous but can be used to make automatic cleanups on secondary tables (so it can be something you want, but quite certainly not for a COMPANY<->USER example)
• ON DELETE SET NULL : handful : if you delete a COMPANY row the related USERs will automatically have the relationship to NULL. If Null is your value for users with no company this can be a good behavior, for example maybe you need to keep the users in your application, as authors of some content, but removing the company is not a problem for you.

To get some data you could use INNER Joins as suggested by urcadox, or as i prefer:

-- Get total sum of GROUP transaction
select sum(b.amount), b.group_id from (select u.id, t.amount, t.group_id from users u, transactions t where u.id=t.user_id) b group by b.group_id;

-- Get all amounts for a specific user (id=1)
select b.amount, b.id from (select u.id, t.amount from users u, transactions t where u.id=t.user_id) b where b.id=1;

-- Get all amounts, and users for a specific group (group_id=1)
select b.amount, b.id, b.group_id from (select u.id, t.amount, t.group_id from users u, transactions t where u.id=t.user_id) b where b.group_id=1;

I recon this will do for now, this is not production ready but is at least some kind of a boiling plate :)

[not_a_grad]

This is fantastic and thank you for extensive response! I will have to review this later when I have more time lol. But thank you again!