Different Schemas or Different Tables for Synonym Switching












0















We are designing our Synonym Switching ETL process and have questions pertaining to the best way to differentiate the two tables used in this process.



Option #1: Schema Names:




  • Schema_name.synonym_student will point to either Schema_name_01.table_student or Schema_name_02.table_student.


Option #2: Table Names:




  • Schema_name.synonym_student will point to either Schema_name_00.table_student_a or Schema_name_00.table_student_b.


We have found two companies that use the 1st approach. But, none we have found use the 2nd approach. We see some advantages with the 2nd approach namely the fact that we can differentiate the tables easily since the tables are uniquely named at the table level.



We are curious what others have found to be the most effective naming strategy in their Synonym Switching design: Option #1 or #2? And why they chose it and any advice or lessons learned after implementation. Thanks!










share|improve this question














bumped to the homepage by Community 20 mins ago


This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
















  • Post-implementation review: shortly after this question was posted we implemented Option #1 Schema Names. After a year of using this approach, I'm pleased with the decision we made. We have found no issues or drawbacks in differentiating the active versus inactive tables via the schema names.

    – Mike Taylor
    Jul 19 '18 at 17:30











  • The only drop back to using the approach was in using synonyms overall for our Data Warehousing efforts. Appears some products don't handle synonyms as well as view or table objects. So, we've had to deal with those from time to time. For example, Power BI does not show synonym objects in its tree viewer. But, we can directly query the synonym from Power BI. So, not a wall just a hurdle in using synonyms at times.

    – Mike Taylor
    Jul 19 '18 at 17:33
















0















We are designing our Synonym Switching ETL process and have questions pertaining to the best way to differentiate the two tables used in this process.



Option #1: Schema Names:




  • Schema_name.synonym_student will point to either Schema_name_01.table_student or Schema_name_02.table_student.


Option #2: Table Names:




  • Schema_name.synonym_student will point to either Schema_name_00.table_student_a or Schema_name_00.table_student_b.


We have found two companies that use the 1st approach. But, none we have found use the 2nd approach. We see some advantages with the 2nd approach namely the fact that we can differentiate the tables easily since the tables are uniquely named at the table level.



We are curious what others have found to be the most effective naming strategy in their Synonym Switching design: Option #1 or #2? And why they chose it and any advice or lessons learned after implementation. Thanks!










share|improve this question














bumped to the homepage by Community 20 mins ago


This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
















  • Post-implementation review: shortly after this question was posted we implemented Option #1 Schema Names. After a year of using this approach, I'm pleased with the decision we made. We have found no issues or drawbacks in differentiating the active versus inactive tables via the schema names.

    – Mike Taylor
    Jul 19 '18 at 17:30











  • The only drop back to using the approach was in using synonyms overall for our Data Warehousing efforts. Appears some products don't handle synonyms as well as view or table objects. So, we've had to deal with those from time to time. For example, Power BI does not show synonym objects in its tree viewer. But, we can directly query the synonym from Power BI. So, not a wall just a hurdle in using synonyms at times.

    – Mike Taylor
    Jul 19 '18 at 17:33














0












0








0








We are designing our Synonym Switching ETL process and have questions pertaining to the best way to differentiate the two tables used in this process.



Option #1: Schema Names:




  • Schema_name.synonym_student will point to either Schema_name_01.table_student or Schema_name_02.table_student.


Option #2: Table Names:




  • Schema_name.synonym_student will point to either Schema_name_00.table_student_a or Schema_name_00.table_student_b.


We have found two companies that use the 1st approach. But, none we have found use the 2nd approach. We see some advantages with the 2nd approach namely the fact that we can differentiate the tables easily since the tables are uniquely named at the table level.



We are curious what others have found to be the most effective naming strategy in their Synonym Switching design: Option #1 or #2? And why they chose it and any advice or lessons learned after implementation. Thanks!










share|improve this question














We are designing our Synonym Switching ETL process and have questions pertaining to the best way to differentiate the two tables used in this process.



Option #1: Schema Names:




  • Schema_name.synonym_student will point to either Schema_name_01.table_student or Schema_name_02.table_student.


Option #2: Table Names:




  • Schema_name.synonym_student will point to either Schema_name_00.table_student_a or Schema_name_00.table_student_b.


We have found two companies that use the 1st approach. But, none we have found use the 2nd approach. We see some advantages with the 2nd approach namely the fact that we can differentiate the tables easily since the tables are uniquely named at the table level.



We are curious what others have found to be the most effective naming strategy in their Synonym Switching design: Option #1 or #2? And why they chose it and any advice or lessons learned after implementation. Thanks!







oracle data-warehouse etl synonyms






share|improve this question













share|improve this question











share|improve this question




share|improve this question










asked Mar 20 '17 at 22:26









Mike TaylorMike Taylor

1011




1011





bumped to the homepage by Community 20 mins ago


This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.







bumped to the homepage by Community 20 mins ago


This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.















  • Post-implementation review: shortly after this question was posted we implemented Option #1 Schema Names. After a year of using this approach, I'm pleased with the decision we made. We have found no issues or drawbacks in differentiating the active versus inactive tables via the schema names.

    – Mike Taylor
    Jul 19 '18 at 17:30











  • The only drop back to using the approach was in using synonyms overall for our Data Warehousing efforts. Appears some products don't handle synonyms as well as view or table objects. So, we've had to deal with those from time to time. For example, Power BI does not show synonym objects in its tree viewer. But, we can directly query the synonym from Power BI. So, not a wall just a hurdle in using synonyms at times.

    – Mike Taylor
    Jul 19 '18 at 17:33



















  • Post-implementation review: shortly after this question was posted we implemented Option #1 Schema Names. After a year of using this approach, I'm pleased with the decision we made. We have found no issues or drawbacks in differentiating the active versus inactive tables via the schema names.

    – Mike Taylor
    Jul 19 '18 at 17:30











  • The only drop back to using the approach was in using synonyms overall for our Data Warehousing efforts. Appears some products don't handle synonyms as well as view or table objects. So, we've had to deal with those from time to time. For example, Power BI does not show synonym objects in its tree viewer. But, we can directly query the synonym from Power BI. So, not a wall just a hurdle in using synonyms at times.

    – Mike Taylor
    Jul 19 '18 at 17:33

















Post-implementation review: shortly after this question was posted we implemented Option #1 Schema Names. After a year of using this approach, I'm pleased with the decision we made. We have found no issues or drawbacks in differentiating the active versus inactive tables via the schema names.

– Mike Taylor
Jul 19 '18 at 17:30





Post-implementation review: shortly after this question was posted we implemented Option #1 Schema Names. After a year of using this approach, I'm pleased with the decision we made. We have found no issues or drawbacks in differentiating the active versus inactive tables via the schema names.

– Mike Taylor
Jul 19 '18 at 17:30













The only drop back to using the approach was in using synonyms overall for our Data Warehousing efforts. Appears some products don't handle synonyms as well as view or table objects. So, we've had to deal with those from time to time. For example, Power BI does not show synonym objects in its tree viewer. But, we can directly query the synonym from Power BI. So, not a wall just a hurdle in using synonyms at times.

– Mike Taylor
Jul 19 '18 at 17:33





The only drop back to using the approach was in using synonyms overall for our Data Warehousing efforts. Appears some products don't handle synonyms as well as view or table objects. So, we've had to deal with those from time to time. For example, Power BI does not show synonym objects in its tree viewer. But, we can directly query the synonym from Power BI. So, not a wall just a hurdle in using synonyms at times.

– Mike Taylor
Jul 19 '18 at 17:33










1 Answer
1






active

oldest

votes


















0














They both will work similarly. I think this is more of a logical question. Unless all the other associated objects reside in separate schemas, it would make sense to keep them in the same schema. That's how we have handled synonym flipping on all my projects. It will make it easier for others who are not familiar to the process to easily see and understand the process.






share|improve this answer























    Your Answer








    StackExchange.ready(function() {
    var channelOptions = {
    tags: "".split(" "),
    id: "182"
    };
    initTagRenderer("".split(" "), "".split(" "), channelOptions);

    StackExchange.using("externalEditor", function() {
    // Have to fire editor after snippets, if snippets enabled
    if (StackExchange.settings.snippets.snippetsEnabled) {
    StackExchange.using("snippets", function() {
    createEditor();
    });
    }
    else {
    createEditor();
    }
    });

    function createEditor() {
    StackExchange.prepareEditor({
    heartbeatType: 'answer',
    autoActivateHeartbeat: false,
    convertImagesToLinks: false,
    noModals: true,
    showLowRepImageUploadWarning: true,
    reputationToPostImages: null,
    bindNavPrevention: true,
    postfix: "",
    imageUploader: {
    brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
    contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
    allowUrls: true
    },
    onDemand: true,
    discardSelector: ".discard-answer"
    ,immediatelyShowMarkdownHelp:true
    });


    }
    });














    draft saved

    draft discarded


















    StackExchange.ready(
    function () {
    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f167682%2fdifferent-schemas-or-different-tables-for-synonym-switching%23new-answer', 'question_page');
    }
    );

    Post as a guest















    Required, but never shown

























    1 Answer
    1






    active

    oldest

    votes








    1 Answer
    1






    active

    oldest

    votes









    active

    oldest

    votes






    active

    oldest

    votes









    0














    They both will work similarly. I think this is more of a logical question. Unless all the other associated objects reside in separate schemas, it would make sense to keep them in the same schema. That's how we have handled synonym flipping on all my projects. It will make it easier for others who are not familiar to the process to easily see and understand the process.






    share|improve this answer




























      0














      They both will work similarly. I think this is more of a logical question. Unless all the other associated objects reside in separate schemas, it would make sense to keep them in the same schema. That's how we have handled synonym flipping on all my projects. It will make it easier for others who are not familiar to the process to easily see and understand the process.






      share|improve this answer


























        0












        0








        0







        They both will work similarly. I think this is more of a logical question. Unless all the other associated objects reside in separate schemas, it would make sense to keep them in the same schema. That's how we have handled synonym flipping on all my projects. It will make it easier for others who are not familiar to the process to easily see and understand the process.






        share|improve this answer













        They both will work similarly. I think this is more of a logical question. Unless all the other associated objects reside in separate schemas, it would make sense to keep them in the same schema. That's how we have handled synonym flipping on all my projects. It will make it easier for others who are not familiar to the process to easily see and understand the process.







        share|improve this answer












        share|improve this answer



        share|improve this answer










        answered Mar 21 '17 at 21:44









        EliEli

        296




        296






























            draft saved

            draft discarded




















































            Thanks for contributing an answer to Database Administrators Stack Exchange!


            • Please be sure to answer the question. Provide details and share your research!

            But avoid



            • Asking for help, clarification, or responding to other answers.

            • Making statements based on opinion; back them up with references or personal experience.


            To learn more, see our tips on writing great answers.




            draft saved


            draft discarded














            StackExchange.ready(
            function () {
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f167682%2fdifferent-schemas-or-different-tables-for-synonym-switching%23new-answer', 'question_page');
            }
            );

            Post as a guest















            Required, but never shown





















































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown

































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown







            Popular posts from this blog

            Liste der Baudenkmale in Friedland (Mecklenburg)

            Single-Malt-Whisky

            Czorneboh