4

People are obviously having trouble understanding this question: Effect of denormalizing

  • Why is it unclear / what unclear about it?

  • What ought I to do (or ought to have done) to make it clearer?

  • Is there a policy or prefered format for database-design-related questions (e.g. include the DDL or a schema diagram in the question)? Would that help in this case or is there something else that's unclear?

I feel like I've posted half a dozen clarifications in comments, to several different people, when I thought the question was clear (to me at least) already.

Clearly my question is therefore inadequate; but I don't know in what way / how to improve it.

On the other hand it's being up- and not down-voted, so I really don't know.

1 Answer 1

4

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

4
  • 1
    Excelent. An indication of which is the PK of UserAccount would also be great, since its never made clear whether the uniqueness in that table is provided only by the unique constraint on UserID or there is a PK composed of UserID plus AccountID. Jan 4, 2015 at 21:53
  • I've added some pos there. One of the nice bits about ascii flow is that it recognizes the text as a drawing. You click the 'import' (top right menu bar), paste the text in, and then you can use the resize tool (arrow through rectangle) and resize it. But anyways, you get the idea.
    – user40980
    Jan 5, 2015 at 0:10
  • Does it have a "move" tool? Jan 5, 2015 at 0:15
  • @user61852 The arrow lets you select an area and move that text. You may have to redraw lines.
    – user40980
    Jan 5, 2015 at 0:48

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .