Hope you’re getting over your battle with tonsillitis.
In terms of the @ - that’s the naming convention recommended by the SQLbi guys to identify columns in virtual tables, avoiding the usual ambiguity as to whether [Overdue] is a measure or a column in a virtual table. For more explanation, see the attached article below. If you work with virtual table variables, I highly recommend adopting this convention.
Your second question about whether merging the tables is “best” is much tougher to answer. There are clearly some objectively wrong ways to construct a data model, but the “right” or “best” way often depends on the analyses you want to do and the questions you want to answer. A structure that works well for one set of questions/analyses may not work as well for a different set. When I start data modeling for any project, I always first think of the analyses I want to produce and then work backwards to the structure that best supports those analyses, even to the point of storyboarding my reports before I engage in any data modeling. That way, as I’m modeling I can think of the measures I’ll need to generate and then evaluate my data model alternatives in terms of how easy or difficult it will be to calculate those measures. That’s probably an unsatisfying answer, but I think there is a lot of art to data modeling that doesn’t lend itself to hard and fast rules.
Generally the rules to stick to are:
- strive to conform to a star schema
- avoid bidirectional relationships
- build your fact table(s) long and narrow (lots of rows, relatively small number of columns)
Beyond that it really is what works best for your individual project requirements.
I hope that’s helpful.
- Brian