2

When designing a view, would it be best to retrieve

  • the ID fields where FK relationships exist between the joined tables
  • the desired description field obtained through the FK id relationship
  • both?

The views I am writing will be primarily used for SSRS reports and I want the views to be adaptable and flexible for multiples uses.

Critiques/advice are encouraged!

Thank you!

| improve this question | |
  • 1
    I'd throw it all on there. You definitely need the description, so joining now, in the view I think would be preferable. And data's (usually) cheap - so just throw the FK on there too. If you don't wind up using later, then no big deal. Your time to add it in later is worth a lot more than the bandwidth cost of all those 32 bit integers – Adam Rackis Jan 12 '12 at 20:14
3

I would add both as Adam points out.
I initially thought that views would only be useful as a base for reports but users end up asking for more detail. The time to add a new column later is much more effort than adding it to start.

| improve this answer | |
2

When designing a view, would it be best to retrieve the ID fields where FK relationships exist between the joined tables

If the "ID fields" in question are surrogate keys then I can envisage a view that would hide the meaningless surrogate key values and only expose the natural keys values for which they are surrogates (perhaps the "desired description field" you mention is the natural key?) In other words, the intention of such a view would be be the opposite of what you propose.

P.S. A further requirement I would propose is that a view should satisfy 1NF.

| improve this answer | |
  • I am not familiar with the term "surrogate key" so I looked it up on Wikipedia (en.wikipedia.org/wiki/Surrogate_key). If I understand correctly, a surrogate key is ultimately used to represent an entity and is usually the primary key but always. The primary key always represents a single record, but the surrogate key always represents an entity. If that is correct, then I am only asking about primary keys. – Jesslyn Jan 13 '12 at 13:46
  • I believe onedaywhen is specifically using "surrogate key" to refer to keys that do not have business meaning. E.g., if your report is about invoices, you probably want to show InvoiceID ''if'' it has a business meaning. If it's just an IDENTITY column used for joins, which users do not need, then hide it; it's just clutter to them. – Jon of All Trades Mar 7 '12 at 23:18
1

What else are you expecting to gain out of the views? I would suggest using a stored procedure over a view. This way it can be optimized better, made more customizable (e.g. parameters or even different query paths). A view that satisfies multiple reports is just asking for trouble IMHO - it ends up carrying extra baggage for one report that isn't needed for others...

| improve this answer | |
  • I want to be able to provide some basic data sets that can be built upon, as necessary, as existing SSRS reports evolve. My office has a number reports that require some of the same data but get their data set from their own dedicated stored procedure. This setup results in seemingly unnecessary code. If it is bad practice to add onto data sets from views in SSRS using more queries then I would agree with you, but I am not sure if reports should contain a little SQL as possible or not. – Jesslyn Jan 12 '12 at 19:53
  • I'm not sure what "add onto data sets" means so maybe I'm not the best person to comment. I do know that centralizing the location of the SQL code is going to be better than having some SQL code in the report and some SQL code in a view. Reverse engineering that is going to be a nightmare. You can also fix query logic etc. in the stored procedure(s) without having to re-deploy reports (unless the change means adding or removing columns). – Aaron Bertrand♦ Jan 12 '12 at 20:05
  • In hindsight, I think I misspoke in "add onto data sets." What I had originally intended was to create a data set in SSRS that contains a query that references the view with join statements to whatever other tables needed for the report. I definitely agree with you that SQL code should be in a centralized location. With that said, it seems more prudent to rely on stored procedures. What is your take on designing a view to be referenced by multiple stored procedures? – Jesslyn Jan 12 '12 at 20:20
  • Again, I think it largely depends. Will the view return the same columns to each stored procedure? Are there joins required by some procedures but not all? Does the view achieve a significant level of simplicity compared to just performing the join in the stored procedure(s)? – Aaron Bertrand♦ Jan 12 '12 at 20:34
  • Yes to every one of your questions :) – Jesslyn Jan 12 '12 at 20:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.