I disagree strongly.
I am not making any assumption about the structure of the Users table or what might happen to it in the future beyond the fact that it will have a Primary Key called 'UserID'. That is not unreasonable.
In the case where a module needs information about the User it is entirely logical to assume the module will reference the User object and leverage the DNN Profile API instead ot duplicating User data in its own table and having to worry about syncing the same data across mutlple table. It is also not unreasonable to assume that the module would want to keep referential integrity intact on any tables that included User related data such that if a User were deleted via the DNN API, that any data in third party modules would also be cascade deleted.
There simply MUST be some mechanism by which a module developer can reference core entities such as User and know that when those entities are deleted using the frameworks API, that related data in custom modules will also be deleted correctly. Unfortuntely, the framework provide no mechanism for hooking into that event so module developers are forced to rely in foreign key definitions to keep referential integrity intact.
Also, as for the DB provider... while I agree that its a great model, the reality is that probably 99.99% of all DNN installations are using SqlServer. I do DNN consulting all day every day for thepast 6 years and I have yet to see a single client using anything other than SqlServer with DNN. The reality is that of the hundreds (thousands?) of modules out there, almost none of them come with any other provider other than SqlServer. While I am sure there are some outliers out there, they are few and far between. So while its great to talk about the theoretical model, it doesn't really match up with what has been implemented in reality.
Anyways, we can debate round and round about the proper way module developer SHOULD have been referencing and handling RI for core entities like Users or Modules, but the only thing we need to realize right now is the framework does not provide any functionality for hooking into a delete event for a core entity AND for the past 6 years, module developers have been handling this requirement by including foreign key constraints that cascade deletes based on the expected behavior of hard deletes on the User table. For all of those modules, this is a breaking change and will prevent them from upgrading to DNN 5.1. Simply saying all those hundreds of modules made a mistake and now any portal that used them are out of luck is not the proper response.
I really only see two possible solutions:
1) Revert the bahavior back to hard deletes
2) Complete the feature set of providing soft deletes by including the functionality to A) clear out sof deleted records similar to the Recycle Bin and B) restore records that have been soft deleted. Withouth this basic level of functionality, having soft deletes is not very useful.
I suggest #1 until #2 can be completed.