I guess I'll have to post the dissenting opinion. While I agree that any system of solution can be made overly complex and difficult to deal with if you incorporate too many obscure features and techniques, nothing replaces good, solid documentation and best demonstrated practices.
Rather, I would approach the problem differently:
- Why is multiple ownership being considered? Is it "just because" or if there a valid, strategic reason for the approach?
- Are the associated technologies truly taking advantage of multiple ownership, or are there further work arounds in place? You want to make sure, that the strategy yields multiple dividends, and are not case specific.
- How "good" is the documentation and how well is the solution (problem space) understood by the team. Anyone who would step right into code to debug this type of problem (without capturing a trace first), clearly needs to take a step back.
- I'd have to say that qualifying a table is mandatory, unless the design explicitly called for it to be unqualified. Excuses about readability, effort, bigger code (really?? Are we back on 64k machines??) are just noise. Most of us are being paid decent coin to build these systems, so let's do it right!
Now when can it be beneficial?
- Multiple ownership strategies is a pretty good way to implement row level security, particularly when there's clear, role based requirements. For example, often, I've used multiple "view sets", to constrain results, in particular in a reporting environment.
- This approach can be used when you're implementing overlapping customizations, again based on roles (or clients). In particular, it becomes easy to activate / deactivate a client simply by controlling the schema.
- In the past, I've happily supported hundreds of developers in a single database, letting them create their own tables for development and testing purposes (under their own account). Sure it's chaos. So what? They know the rules of the game and are funneled and constrained as they move into system test and certainly Quality Assurance. Keeps them happy and productive, and fosters some innovation. If it means I need to keep on my toes, while part of my job is to serve them as well!
Ok, to dial this back a little. Anyone who uses any feature or capability of the environment without clear justification and understanding needs to be drummed out of the business. That includes object ownership. If there's not a good reason to use schemas other than dbo, that dammit don't do it. And while we're at it, let's consider alternate approaches and additional issues like recoverability, auditability, and maintenance (yes, multiple versions of a table can be a pain if you have to update them). Developer won't do the documentation? Have a friendly chat with their boss (and escalate nicely). Draw up a "form" that identifies the risks and have the boss sign it. That'll get action.
If I have a choice between mySchema.myTable and dbo.myScheam_myTable, i'll take the first one, every time. But, if the same requirements could be satisfied by adding a simple column to the table (i.e. AccessRole), then wow, let's do the simpler approach. Really depends on the application, and what you're trying to achieve.