Assume I have a schema defined with the 4 following entities:
-> Timeline (fk: userId)
-> Entries (fk: timelineId)
-> Tags (fk: entryId), where fk means foreign key.
Now, let's say I want to check in the web application if a user has permission to delete a particular tag. Right now I use Basic Authentication, check if the user's email // password exist in the database, and if so, grab the userId.
Because of the userId only existing on the Timeline entity, I feel like I'd need to do the following:
DELETE t.* FROM `tags` AS t
INNER JOIN `entries` AS e ON t.entryId = e.id
INNER JOIN `timelines` AS tl ON e.timelineId = tl.id
tl.userId = ? AND
t.id = ?
This approach works, but I feel like it would be inefficient. Instead, I could add a userId FK to every single table such as the tags, but that also seems like a maintenance nightmare.
I can't think of any other approaches other than implementing some other type of permission system, such as using an ACL. Any suggestions?