I'm a beginner Beginner programmer, i just jumped into creating an app with zero previous experience with flutter or sql, so what I'm imagining might be the worst method for what I need.

Basically I'm making a sort of task tracking app for study groups, and i want to have a table with all users' authentication info, and a table for study groups (groups of multiple users) which will have the password for joining it and the subjects the students are learning.

My problem is that i can't figure out the best way to store not only the names of the subjects that the users input themselves, but the possibly long list of tasks they'll add for each category they create. I'd like it to be tied to the study group table somehow, so if another student joins, they can see everything right away. Do i have a table with every tasks ever made and tie it to the study groups table and call it with their unique id? Such as (id, task, subject, study group id)? There has to be a more efficient way since there can be an infinite number of tasks that will come and go.

So right now it's just table A for the groups (id, group name, group pass, user 1, user 2, user3, user 4) and table B for users (id, username, fname, authentication info).

Basically the question is, if you had to do this, would you make a separate table for the tasks and with what kind of foreign key, or..?

Sorry if this didn't make sense. If anyone can think of a more appropriate way to do this, I'd be grateful.

CodePudding user response：

This should get you an idea on how you might want to design your db. This is a simple ERD but you can expand as needed. Look up db design and how cardinality work as well.

A user can have zero up to multiple tasks. A user can belong to a single group. A group can have multiple users.

FK = Foreign Key

PK = Primary Key