One of the toughest challenges I have faced in Enterprise Application Architecture is on achieving modularity in Database design.
Typically you need to retrieve data based on filter conditions depending on tables across the modules. You can write SQL queries joining tables from multiple modules.
You have three options now; (a) allow this; you end up compromise modularity and finding out dependencies become difficult. (b) prohibit this; data retrieval will become less efficient. (c) Yet another approach could be duplicating frequently used static data in both modules.
Probably combination (b) and (c) could be good approach! Allow some data duplication across the modules; Compromise some performance for the sake of modularity.
Here it is very critical to decide the boundaries of the modules very wisely. If you have too fine grained modules performance impact and data duplication will too much. If decide too course grained modules the maintainability will be reduced (impact analysis of change will be difficult)
In most of the applications you will have n number of functional modules and one master module (module which contain entities like users, departments, locations etc). You need to access data from master module in all functional modules. The interaction between master data and functional module will be too large to consider as separate modules. All functional modules will have such dependency with master data. (a) One approach could be to consider master data as part of all modules (disadvantage of this will be that any change in master data design will have impact across the application); (b) another approach is to consider master data as an independent module (disadvantage of this shall be the performance loss and data duplication). I would prefer approach (a)