Different Schemas or Different Tables for Synonym Switching
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
asked Mar 20 '17 at 22:26
Mike TaylorMike Taylor
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.
add a comment |
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
asked Mar 20 '17 at 22:26
Mike TaylorMike Taylor
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.
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
add a comment |
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
asked Mar 20 '17 at 22:26
Mike TaylorMike Taylor
1011
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
oracle data-warehouse etl synonyms
asked Mar 20 '17 at 22:26
Mike TaylorMike Taylor
1011
asked Mar 20 '17 at 22:26
Mike TaylorMike Taylor
1011
asked Mar 20 '17 at 22:26
Mike TaylorMike Taylor
1011
asked Mar 20 '17 at 22:26
Mike TaylorMike Taylor
1011
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
add a comment |
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
add a comment |
1 Answer
1
active
oldest
votes
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.
answered Mar 21 '17 at 21:44
EliEli
296
add a comment |
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
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
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
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.
answered Mar 21 '17 at 21:44
EliEli
296
add a comment |
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.
answered Mar 21 '17 at 21:44
EliEli
296
add a comment |
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.
answered Mar 21 '17 at 21:44
EliEli
296
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.
answered Mar 21 '17 at 21:44
EliEli
296
answered Mar 21 '17 at 21:44
EliEli
296
answered Mar 21 '17 at 21:44
EliEli
296
answered Mar 21 '17 at 21:44
EliEli
296
296
add a comment |
add a comment |
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.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
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
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
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
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