I have a 'notes' table with:
int primary key 'id'
date 'date'
longtext 'text'
And then I have a lot of tables for different kinds of items that can all potentially have multiple unique notes associated with them (Notes are never shared between items). Each one of these tables has a relational table like 'item_note' to link to notes in the notes table, like:
int primary key 'id'
int 'item_id'
int 'note_id'
I'd like to create table/edit views for these various item tables that pull in all of the notes associated with each row, and in the edit view rather than picking from a list of already-created notes like the default n-to-n edit view, I want to let the user create new notes right then and there that will add to the existing notes and relational table, as well as (potentially) delete note items.
Is this possible to do in a reasonable manner? Or am I close but there's a better way to go about this than what I'm trying to do? If a solution requires me to redo my schema a bit I'm willing to (Ugh, one big notes table with an extra item_type column?), but it'd be nice if it could work with the current setup.
Basically I'd like to be able to add and delete infinite notes on items within their edit views for the convenience of my users.
Thank you!