How to link sql database entries?

Question

I have a SQLite database of notes that have columns _id, title, details, listid

_id is the auto incremented primary key
title and details are string data fields
listid is a foreign key pointing to a list name in another table.

I'd like to find a way to have notes that are in multiple lists or notes that are linked in such a way that updating one will update the other or be edited simultaneously by some other means.

The overall goal is to have copies of the same note in multiple lists where you edit one and the rest update automatically.

I've thought of adding an extra column with a sort of link id that will be shared by all linked notes, creating a way to update other notes.

Answer 1

One note can have many lists, One list can have many notes.

you need an associative table that has a note id and a list id

Answer 2

SQLite 3.6.19+ natively supports (and enforces) Foreign Keys, see SQLite Foreign Key Support.

Answer 3

Have three tables:

NOTE: _id, title, details

LIST: _id, listname

NOTES_IN_LIST: note_id, list_id

Then whenever you add a note to a list, you add a new row to NOTES_IN_LIST that connects that note ('s note_id) to the list ('s list_id).

Whenever you edit a note, you just edit it in the NOTE table.

Whenever you list the contents of the list that you have the id for, you do a SELECT something like:

SELECT title, details
from NOTE
where NOTE._id in (
    SELECT note_id from NOTES_IN_LIST
    where list_id=<your list id>
)

or

SELECT title, details
from NOTE, NOTES_IN_LIST
where
    NOTE._id=NOTES_IN_LIST.note_id
    and
    NOTES_IN_LIST.list_id=<your list id>

---

Hmm, to transfer old notes to new structure, I would:

• create a new notes table with a new autoincrement id field
• then select distinct (note title, note details) into that new notes table
• then join the old notes table to the new notes table on old_title=new_title and old_detail=new_detail, then select from that the new note id and the old list id, then insert the resulting table into the NOTES_IN_LIST table
• then I think you can delete the old notes table

Make sure noone edits or adds notes while this is happening, or you will lose notes.

Also you will need to update the UI to work into the new notes table, put notes to lists not by copying but by inserting a new row into NOTES_IN_LIST, etc.