(Apologies for any bad formatting or ignorance of common practices within this community; this is my first question here.)
I'm attempting to (re)design a MySQL database for a personal social networking site I'm developing. Quite often, I run into situations where there is an element (such as a "like" button) that requires a globally unique id despite being associated with a variety of different "parent" element types (blog post, comment, user page, etc). Previously, my design had been giving every like button a column for
user_id, and leaving the irrelevant columns NULL; if a like button is on a blog post with id 5,
blog_id would be set to 5 while
This implementation seemed redundant and unmaintainable, so I searched up alternative solutions.
The best solution I found seemed to be to define a new table, say
like_buttons, with a primary key
like_button_id that would be provided in a foreign key column within all other significant tables (
users). Then, whenever a like button's data must be accessed, one can search for the
like_button_id column of the appropriate parent element, and go on from there.
However, when defining new comments and blog posts, I do not wish to have to add another line to my code separately defining a new like button just to pass the id to the significant element. Consequently, I stumbled upon MySQL's trigger functionality in searching for a solution.
To avoid further protracted exposition, my question lies in the acceptability of the following example code:
TRIGGER insert_user_like_button BEFORE INSERT ON users FOR EACH ROW BEGIN INSERT INTO like_buttons VALUES(); SET NEW.like_button_id = LAST_INSERT_ID(); END
This trigger would be repeated for every other parent element table, such as
like_buttons (in this example case) only has one
id NOT NULL PRIMARY KEY AUTO_INCREMENT column, is this the best method for preserving uniqueness in ids across multiple tables of information? Is there any more elegant, efficient, extendable, or appropriate solution?