SQL Database design help needed

Question

I am trying to limit the about of tables in my database because I hear that's good (I am a novice web developer). I want to have the user input a list into the database. So they input a title, overall comment, and then start making the list. I can't figure out how to do this without making a new table for each list. Because, say one user wants a list with 44 values and another user wants a list of 10 values. I can't think of how to do this without making a new table for each list. I would really appreciate any help/insight you can give to me, thanks in advance.

Answer 1

As a quick answer, rather than trying to have a separate column for each element in the list, you could have a single column titled say 'list'. This column could be delimited and contain all the values in your list. Your back-end code could then parse out the individual elements for you to do as you wish with them. That way your table size remains constant.

Answer 2

Basically, you want to make a table for the user lists, where each row in the table refers to one user's lists, and another table for the user list values, where each row in the table has a column for a reference to the list it belongs to, and a column for the value the user input.

Answer 3

Your Table Could Be: UserID, int ListID, int (Primary Key-Unique Identifier) Title, VarChar(250) Comment, VarChar(MAX)

Example Content:
1  | 1  | The Title        | My Comment
1  | 2  | The Other Title  |  My other comment
2  | 3  | First Comment    | Second Person, first comment

Eacher User just gets their list from a query:

Select ListID, Titel, Comment FROM the_Table where UserID = @UserID

Answer 4

You can get away with a single table of lines for all the lists, say for example simply

CREATE TABLE ListLines (
    listID INTEGER,
    lineNo INTEGER,
    line TEXT,
    PRIMARY KEY (listID, lineNo),
    FOREIGN KEY (listID) REFERENCES Lists
);

with the table of lists becoming:

CREATE TABLE Lists (
    listID INTEGER PRIMARY KEY,
    userID INTEGER,
    title TEXT,
    comment TEXT,
    FOREIGN KEY (userID) REFERENCES Users
);

assuming you have a Users table with primary key userID INTEGER with per-user information (name, etc, etc).

So to get all the lines of a list given its ID you just

SELECT line FROM ListLines
 WHERE listID=:whateverid
ORDER BY lineNo;

or you could UNION that with e.g. the title:

SELECT title AS line FROM Lists
 WHERE listID=:whateverid
UNION ALL
SELECT line FROM ListLines
 WHERE listID=:whateverid
ORDER BY lineNo;

and so on. This flexible and efficient arrangement is the relational way of doing things...