The text description of the tables, while rather thorough is something that can be difficult for someone who can't actually type describe Users to see what the structure is and how it relates to each other.
For this, I like using http://asciiflow.com because it allows for an ascii art representation of the structure making it easier for others to copy and modify as necessary.
+-----------+ +--------------+ +--------------+
| Users | | UserAccount | | Account |
+-----------+ +--------------+ +--------------+
+--+ userId pk +--+ userId (uniq)| +--> accountId pk |
| | | | accountId +---+ | |
| +-----------+ +--------------+ +--------------+
|
|
| +-----------+
| | Sales |
| +-----------+ (and se^eral other tables like this)
| | saleId pk |
+--+ userId fk |
| |
+-----------+
There is no policy mandating the DDL, but yes, having the DDL or a ER diagram, but they do help in clearly and rapidly conveying the information in well understood means that doesn't have ambiguity of text associated with it. That diagram covers the all the bullet points of the question.
Then, the question can be boiled down to:
I can't modify the Users table for various reasons. When doing a query against a Sales table that needs to include the Account information, I need to write a query such as:
select A.*, U.*
from Sales S
join Users U on U.userId = S.userId
join UserAccount UA on U.userId = UA.userId
left join Account A on UA.accountId = A.accountId
where
S.something = somethingElse
This query has a lot of joins in it that that is slightly troubling to me. One approach to this would be to denormalize the Sales table so it also includes the accountId in it. This is also slightly troubling.
What is the cleaner approach to writing this so that I don't keep having all of those joins in the queries?
You could have some other information there, but this really condenses down the question into something that is more clear. It describes the table structure, the problem you are having, and what solution you are looking for.
Noting that it can be difficult to get at the source of a meta answer if you don't have 2k rep to be able to edit, it: the raw revision text
