What is the standard for storing template items, tenant items, and instance items in SQL Server?
$begingroup$
Looking for the best database design
Suppose I have a database with some companies and employees in it. I also have some entities which have a inheritance relationship. In other words the fields are always going to be the same for any type save for a couple properties. The application is a multi-tenant application where different companies are the tenants.
Master Template - The master exam is simple it stores a default of what the company might be using.
Company Template - The company exam template is a tenant specific exam template which can be updated to the company's liking based on a brand new instance of template or a copy of the master template. This creates a horror if the templates have to be related back to their originator(either company or master although some constraints could be put in to for a new company template for every exam or have a optional foreign key for each type in the exam class).
Exam - The instances are saved when a user within the system decides to take the exam and it needs to be stored for later retrieval. All items are flexible except the instances of exam which are a snapshot of the employee's data once the exams are completed.
Table Per Hierarchy (TPH) - I am interested in this design since the fields will always overlap and each time I create a new template or entity I can copy all the fields from the parent to create a new child item and the database doesn't get cluttered with named tables. However, the exam instances do have other metadata that is not necessary for the templates.
public class MasterTemplate { }
public class CompanyTemplate : MasterTemplate { }
public class Exam : MasterTemplate { } or : CompanyTemplate {
// Extra properties not needed by templates(TPH Fail).
}
Another idea, is to create exam templates table and Exam Table where the templates table is shared between the master templates and the company templates either using TPH or a foreign key to differentiate the items. This also brings up another question I will post shortly after this one which is, "Is it a bad idea to store template data in the same table as instance data via foreign key?"
Table Per Hierarchy for templates only
public class MasterExam {
// Shared properties here not much overlap(maybe)
}
public class CompanyExam : MasterExam { }
public class Exam { }
Table Per Type - This makes for a cluttered database and more chances to mess up naming of columns to all be the same.
public class ExamTemplate { }
public class CompanyExam { }
public class Exam { }
There are some other design considerations as well.
What happens when the user wants to fall back on default for the parent(either master or company)?
What problem am I going to run into using any of these database designs.
c# sql database
asked 1 hour ago
jwizejwize
14715
$endgroup$
add a comment |
$begingroup$
Looking for the best database design
Suppose I have a database with some companies and employees in it. I also have some entities which have a inheritance relationship. In other words the fields are always going to be the same for any type save for a couple properties. The application is a multi-tenant application where different companies are the tenants.
Master Template - The master exam is simple it stores a default of what the company might be using.
Company Template - The company exam template is a tenant specific exam template which can be updated to the company's liking based on a brand new instance of template or a copy of the master template. This creates a horror if the templates have to be related back to their originator(either company or master although some constraints could be put in to for a new company template for every exam or have a optional foreign key for each type in the exam class).
Exam - The instances are saved when a user within the system decides to take the exam and it needs to be stored for later retrieval. All items are flexible except the instances of exam which are a snapshot of the employee's data once the exams are completed.
Table Per Hierarchy (TPH) - I am interested in this design since the fields will always overlap and each time I create a new template or entity I can copy all the fields from the parent to create a new child item and the database doesn't get cluttered with named tables. However, the exam instances do have other metadata that is not necessary for the templates.
public class MasterTemplate { }
public class CompanyTemplate : MasterTemplate { }
public class Exam : MasterTemplate { } or : CompanyTemplate {
// Extra properties not needed by templates(TPH Fail).
}
Another idea, is to create exam templates table and Exam Table where the templates table is shared between the master templates and the company templates either using TPH or a foreign key to differentiate the items. This also brings up another question I will post shortly after this one which is, "Is it a bad idea to store template data in the same table as instance data via foreign key?"
Table Per Hierarchy for templates only
public class MasterExam {
// Shared properties here not much overlap(maybe)
}
public class CompanyExam : MasterExam { }
public class Exam { }
Table Per Type - This makes for a cluttered database and more chances to mess up naming of columns to all be the same.
public class ExamTemplate { }
public class CompanyExam { }
public class Exam { }
There are some other design considerations as well.
What happens when the user wants to fall back on default for the parent(either master or company)?
What problem am I going to run into using any of these database designs.
c# sql database
asked 1 hour ago
jwizejwize
14715
$endgroup$
2
$begingroup$
I'm sorry this question is off-topic. We review concrete implementations from a project. We don't help you make design decisions before writing the implementations
$endgroup$
– bruglesco
1 hour ago
$begingroup$
Then where do I post this?
$endgroup$
– jwize
58 mins ago
$begingroup$
I would recommended you read What topics can I ask about here? and How do I ask a good question?
$endgroup$
– Svek
52 mins ago
$begingroup$
I would maybe try dba.stack exchange.com -- What topics can I ask about here? and if reworded better it might just pass over at StackOverflow (but I doubt it could be reworded to qualify for there too).
$endgroup$
– Svek
48 mins ago
add a comment |
$begingroup$
Looking for the best database design
Suppose I have a database with some companies and employees in it. I also have some entities which have a inheritance relationship. In other words the fields are always going to be the same for any type save for a couple properties. The application is a multi-tenant application where different companies are the tenants.
Master Template - The master exam is simple it stores a default of what the company might be using.
Company Template - The company exam template is a tenant specific exam template which can be updated to the company's liking based on a brand new instance of template or a copy of the master template. This creates a horror if the templates have to be related back to their originator(either company or master although some constraints could be put in to for a new company template for every exam or have a optional foreign key for each type in the exam class).
Exam - The instances are saved when a user within the system decides to take the exam and it needs to be stored for later retrieval. All items are flexible except the instances of exam which are a snapshot of the employee's data once the exams are completed.
Table Per Hierarchy (TPH) - I am interested in this design since the fields will always overlap and each time I create a new template or entity I can copy all the fields from the parent to create a new child item and the database doesn't get cluttered with named tables. However, the exam instances do have other metadata that is not necessary for the templates.
public class MasterTemplate { }
public class CompanyTemplate : MasterTemplate { }
public class Exam : MasterTemplate { } or : CompanyTemplate {
// Extra properties not needed by templates(TPH Fail).
}
Another idea, is to create exam templates table and Exam Table where the templates table is shared between the master templates and the company templates either using TPH or a foreign key to differentiate the items. This also brings up another question I will post shortly after this one which is, "Is it a bad idea to store template data in the same table as instance data via foreign key?"
Table Per Hierarchy for templates only
public class MasterExam {
// Shared properties here not much overlap(maybe)
}
public class CompanyExam : MasterExam { }
public class Exam { }
Table Per Type - This makes for a cluttered database and more chances to mess up naming of columns to all be the same.
public class ExamTemplate { }
public class CompanyExam { }
public class Exam { }
There are some other design considerations as well.
What happens when the user wants to fall back on default for the parent(either master or company)?
What problem am I going to run into using any of these database designs.
c# sql database
asked 1 hour ago
jwizejwize
14715
$endgroup$
Looking for the best database design
Suppose I have a database with some companies and employees in it. I also have some entities which have a inheritance relationship. In other words the fields are always going to be the same for any type save for a couple properties. The application is a multi-tenant application where different companies are the tenants.
Master Template - The master exam is simple it stores a default of what the company might be using.
Company Template - The company exam template is a tenant specific exam template which can be updated to the company's liking based on a brand new instance of template or a copy of the master template. This creates a horror if the templates have to be related back to their originator(either company or master although some constraints could be put in to for a new company template for every exam or have a optional foreign key for each type in the exam class).
Exam - The instances are saved when a user within the system decides to take the exam and it needs to be stored for later retrieval. All items are flexible except the instances of exam which are a snapshot of the employee's data once the exams are completed.
Table Per Hierarchy (TPH) - I am interested in this design since the fields will always overlap and each time I create a new template or entity I can copy all the fields from the parent to create a new child item and the database doesn't get cluttered with named tables. However, the exam instances do have other metadata that is not necessary for the templates.
public class MasterTemplate { }
public class CompanyTemplate : MasterTemplate { }
public class Exam : MasterTemplate { } or : CompanyTemplate {
// Extra properties not needed by templates(TPH Fail).
}
Another idea, is to create exam templates table and Exam Table where the templates table is shared between the master templates and the company templates either using TPH or a foreign key to differentiate the items. This also brings up another question I will post shortly after this one which is, "Is it a bad idea to store template data in the same table as instance data via foreign key?"
Table Per Hierarchy for templates only
public class MasterExam {
// Shared properties here not much overlap(maybe)
}
public class CompanyExam : MasterExam { }
public class Exam { }
Table Per Type - This makes for a cluttered database and more chances to mess up naming of columns to all be the same.
public class ExamTemplate { }
public class CompanyExam { }
public class Exam { }
There are some other design considerations as well.
What happens when the user wants to fall back on default for the parent(either master or company)?
What problem am I going to run into using any of these database designs.
c# sql database
c# sql database
asked 1 hour ago
jwizejwize
14715
asked 1 hour ago
jwizejwize
14715
asked 1 hour ago
jwizejwize
14715
asked 1 hour ago
jwizejwize
14715
asked 1 hour ago
jwizejwize
14715
14715
2
$begingroup$
I'm sorry this question is off-topic. We review concrete implementations from a project. We don't help you make design decisions before writing the implementations
$endgroup$
– bruglesco
1 hour ago
$begingroup$
Then where do I post this?
$endgroup$
– jwize
58 mins ago
$begingroup$
I would recommended you read What topics can I ask about here? and How do I ask a good question?
$endgroup$
– Svek
52 mins ago
$begingroup$
I would maybe try dba.stack exchange.com -- What topics can I ask about here? and if reworded better it might just pass over at StackOverflow (but I doubt it could be reworded to qualify for there too).
$endgroup$
– Svek
48 mins ago
add a comment |
2
$begingroup$
I'm sorry this question is off-topic. We review concrete implementations from a project. We don't help you make design decisions before writing the implementations
$endgroup$
– bruglesco
1 hour ago
$begingroup$
Then where do I post this?
$endgroup$
– jwize
58 mins ago
$begingroup$
I would recommended you read What topics can I ask about here? and How do I ask a good question?
$endgroup$
– Svek
52 mins ago
$begingroup$
I would maybe try dba.stack exchange.com -- What topics can I ask about here? and if reworded better it might just pass over at StackOverflow (but I doubt it could be reworded to qualify for there too).
$endgroup$
– Svek
48 mins ago
2
2
$begingroup$
I'm sorry this question is off-topic. We review concrete implementations from a project. We don't help you make design decisions before writing the implementations
$endgroup$
– bruglesco
1 hour ago
$begingroup$
I'm sorry this question is off-topic. We review concrete implementations from a project. We don't help you make design decisions before writing the implementations
$endgroup$
– bruglesco
1 hour ago
$begingroup$
Then where do I post this?
$endgroup$
– jwize
58 mins ago
$begingroup$
Then where do I post this?
$endgroup$
– jwize
58 mins ago
$begingroup$
I would recommended you read What topics can I ask about here? and How do I ask a good question?
$endgroup$
– Svek
52 mins ago
$begingroup$
I would recommended you read What topics can I ask about here? and How do I ask a good question?
$endgroup$
– Svek
52 mins ago
$begingroup$
I would maybe try dba.stack exchange.com -- What topics can I ask about here? and if reworded better it might just pass over at StackOverflow (but I doubt it could be reworded to qualify for there too).
$endgroup$
– Svek
48 mins ago
$begingroup$
I would maybe try dba.stack exchange.com -- What topics can I ask about here? and if reworded better it might just pass over at StackOverflow (but I doubt it could be reworded to qualify for there too).
$endgroup$
– Svek
48 mins ago
add a comment |
0
active
oldest
votes
Your Answer
StackExchange.ifUsing("editor", function () {
return StackExchange.using("mathjaxEditing", function () {
StackExchange.MarkdownEditor.creationCallbacks.add(function (editor, postfix) {
StackExchange.mathjaxEditing.prepareWmdForMathJax(editor, postfix, [["\$", "\$"]]);
});
});
}, "mathjax-editing");
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "196"
};
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%2fcodereview.stackexchange.com%2fquestions%2f211735%2fwhat-is-the-standard-for-storing-template-items-tenant-items-and-instance-item%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
Thanks for contributing an answer to Code Review 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.
Use MathJax to format equations. MathJax reference.
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%2fcodereview.stackexchange.com%2fquestions%2f211735%2fwhat-is-the-standard-for-storing-template-items-tenant-items-and-instance-item%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
2
$begingroup$
I'm sorry this question is off-topic. We review concrete implementations from a project. We don't help you make design decisions before writing the implementations
$endgroup$
– bruglesco
1 hour ago
$begingroup$
Then where do I post this?
$endgroup$
– jwize
58 mins ago
$begingroup$
I would recommended you read What topics can I ask about here? and How do I ask a good question?
$endgroup$
– Svek
52 mins ago
$begingroup$
I would maybe try dba.stack exchange.com -- What topics can I ask about here? and if reworded better it might just pass over at StackOverflow (but I doubt it could be reworded to qualify for there too).
$endgroup$
– Svek
48 mins ago