Creating multiple tables using the main table ID column as a foreign-CodePudding

I am creating a CRUD application which is a To-Do list. My app will have multiple users accessing it and also can handle multiple lists. This is my SQL which creates a table with the lists and the tasks for each list:

CREATE TABLE IF NOT EXISTS Lists(
    Id INT PRIMARY KEY,
    Name TEXT,
    UNIQUE(Id)
)

CREATE TABLE Tasks(
    Id INT NOT NULL,
    txt TEXT,
    listID INT,
    completed BOOLEAN,
    UNIQUE(Id)
    FOREIGN KEY (listID) REFERENCES Lists(Id)
)

My question is how can I create multiple tables with tasks which relate to the lists and also is this the best way to do it or there is a better way to structure my database?

CodePudding user response：

You do not wanna create a new table for each list's tasks. The way you have set it up is the way to do it already. It will look kinda scuffed when you just look at the database, but it will work perfectly after the selection.

If you want to display only the tasks of one list, you will use the listID to select them. Like this:

SELECT txt, completed from Tasks where listID = %CURRENT_LISTID%

%CURRENT_LISTID% will have to be replaced by whichever code calls the SQL to the list you want the tasks off.