-1

I was researching common naming conventions for tables and views and I came across this question: Naming Database Tables and Views

One of the users posted this response:

Using v for view as a standard is particularly bad in my eyes because it prevents you from using one of the best ways of refactoring a database which is to rename the table and create a view with the old name that mimics the old structure so nothing breaks while you are making the change but you can start finding and fixing all the old references without having to fix all of them before the change is put to prod.

I don't think I quite understand how renaming a table and creating a view with the old name that mimics the old structure is all that helpful. What's the end game here? I'm looking for an example which can explain this technique and why it would be helpful.

How would the above technique be useful for the following scenarios:

  1. Table: Users_Tbl; View: Users. I want to rename Users_Tbl but without breaking calls to Users.

  2. Table: Users_Tbl; View: Users. I want to rename Users but without breaking calls to Users.

When I say "breaking calls", I'm referring to SQL jobs, SPs which may leverage tables and views, SSIS packages, server side DB calls, etc..

Thank you for your help.

user3621633
  • 1,681
  • 3
  • 32
  • 46

1 Answers1

1

This question is going to attract opinionated answers, however, this response (which isn't) is too long for a comment.

That's not what the person is getting at. They are saying you have a table called Users (no view). Later for some reason, you need to move to a new object, which'll have a different name. When you do move object, you also create a VIEW call Users, which points to the new object.

Personally, however, I feel the point is a little mute now, as you have Synonyms; so you could just create one of those instead.

As I raised in my opening statement though, naming standards are quite opinionated. Different businesses have different logic and rules. There is no "international" standard, as such but the company you work for may well have one. Though I do suggest against the sp_ prefix: Is the sp_ prefix still a no-no?

Thom A
  • 88,727
  • 11
  • 45
  • 75
  • Good suggestion there, creating a `Synonym` of the object. I wasn't so much looking for naming conventions, because I know that's a debatable topic. My question was strictly on the technique from the other SO question and how that'd be helpful for refactoring. So, If I already have a view pointing to a table, then that technique doesn't really do much for me. My end game might be don't break calls from i.e. Java code, to the SQL view but rename the SQL View. No matter what it sounds like, I'll have to update external code which calls the View at some point. – user3621633 Feb 25 '19 at 17:07
  • 1
    The synonyms are only useful if the "refactoring" was nothing more than a rename. The refactoring might decide to split the `Users` table into two and replace it with a view joining the two tables (possibly with `instead of` triggers) so the interface for some legacy application remains the same. – Martin Smith Feb 25 '19 at 17:40