mysql - centralized "Notes" table for all other tables

Viewed 76

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?

0 Answers
Related