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.
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.
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
SQLite 3.6.19+ natively supports (and enforces) Foreign Keys, see SQLite Foreign Key Support.
精彩评论