Let's look how I would do it. I would proceed from the fact that the user's right to complete deletion is above all. Then I would divide the logic into three parts: Database, Back End and Front End.
From the Database point of view, you are right, the user_id should be nullable. If the user decides to delete himself permanently, you do so. This way you comply with user's rights and do not accumulate "garbage" in the database.
At the Back End level, you fill in the user_id when creating. And if you do not have another entry point for creating books, the user_id is always filled in, since only authenticated users have access to creating books. And only this ID will be able to change the book. If the user_id of a book is empty, then no one can modify that book. Here we can talk about the portal administrator, who will have access to do that, I think this is clear anyway.
The only question left at the Front End level is what to display as the author of the book. Here you need to look at the circumstances dictated, for example, by the agreement between your platform and the author. Let's say in the agreement that the author signs upon registration, you can directly indicate how you will deal with his books when deleting his account:
- delete along with the account;
- appropriate authorship (e.g. display the admin's nickname);
- leave on the site without indicating the author;
- reserve the right to display the author's First and Last Name without reference to his account.
