With MySQL 5.7, I would like to have a table named "Notes" that will contains all note for the whole database. In other words, all tables that have to store a note, will be linked to the Notes table instead of having a field to store it.
There is an example:
This is my main Notes table:
CREATE TABLE Notes (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
fullNote TEXT
);
These tables have field that points to the Notes table:
CREATE TABLE Items (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
[... any additional fields],
note_id INT UNSIGNED );
CREATE TABLE Customers (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
[... any additional fields],
note_id INT UNSIGNED );
I have more tables with "note_id" field.
My question: is it possible to relate all "note_id" field in each tables to the Notes.id table? And, what is the best way to "automate" the deletion of the note when I delete a record in parent other tables
If it's not possible, do you have a better way to store Notes than a field in each tables?