Long story short, I am going to be building a web application with WordPress as the framework. We have to use WordPress, I cannot suggest a different framework.
The application will have users with various roles, and all sorts of custom data and post types. I will use custom post types where I can, but some aspects of the project seem like the best thing to do is create my own tables inside the WordPress database, or, use an entirely separate database.
For example - A single admin can upload up to 6 PDFs on a special admin page. I would want the user to just upload the media like normal. Then I would consider having table called
special_report_pdfs or whatever, and have attachment ids stored there so I can reference "the 4th special pdf" later. This is just one example. I can provide more if needed.
I know adding columns to WordPress tables is considered bad. It also seems like adding tables to the WordPress database is also considered bad? Or does it make the most sense in my case? Is using a separate database acceptable?
My worry is what if there is no hook available for a certain action. From my previous example, anytime a PDF file was deleted from the media library, I would need to hook in, and potentially connect to the other database and delete the reference in the
special_report_pdfs table. If there is no hook for that, is there any way to update foreign keys in my database when certain things in the wordpress database change? Maybe that is why using a separate database is not recommended?