How to multi-match two tables efficiently? The Next CEO of Stack Overflow
Why does sin(x) - sin(y) equal this?
What steps are necessary to read a Modern SSD in Medieval Europe?
How did scripture get the name bible?
Would a grinding machine be a simple and workable propulsion system for an interplanetary spacecraft?
Car headlights in a world without electricity
Free fall ellipse or parabola?
How badly should I try to prevent a user from XSSing themselves?
How to compactly explain secondary and tertiary characters without resorting to stereotypes?
Why can't we say "I have been having a dog"?
Is it "common practice in Fourier transform spectroscopy to multiply the measured interferogram by an apodizing function"? If so, why?
Why was Sir Cadogan fired?
Can a PhD from a non-TU9 German university become a professor in a TU9 university?
Can I hook these wires up to find the connection to a dead outlet?
Incomplete cube
Identify and count spells (Distinctive events within each group)
Is it OK to decorate a log book cover?
Compensation for working overtime on Saturdays
How exploitable/balanced is this homebrew spell: Spell Permanency?
Could a dragon use hot air to help it take off?
What is the difference between 'contrib' and 'non-free' packages repositories?
Is it correct to say moon starry nights?
Can Sri Krishna be called 'a person'?
How can I prove that a state of equilibrium is unstable?
Shortening a title without changing its meaning
How to multi-match two tables efficiently?
The Next CEO of Stack Overflow
I'm relatively new to database-design and want to create a SQLite database for an application. The application needs to handle lots of objects and assign them to a smaller list of categories. Both tables have multiple columns. The problem is, that every object may belong to an indefinite amount of categories and I need to query both efficiently: All objects that belong to a specific category and for any object to which categories it belongs, which is why I can't just use joins.
My idea was to create a third table with just two columns, matching objects to categories. But it looks like an ugly solution to me and I also don't know what indices I should set upon it, if any at all. I might end up with the key column's data being saved four times: In the original table, in its PRIMARY KEY, in the matching table, and in the index on that table.
Is there a better approach or maybe even a standard method to handle such situations?
database-design sqlite
asked 1 min ago
BachsauBachsau
101
New contributor
Bachsau is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
I'm relatively new to database-design and want to create a SQLite database for an application. The application needs to handle lots of objects and assign them to a smaller list of categories. Both tables have multiple columns. The problem is, that every object may belong to an indefinite amount of categories and I need to query both efficiently: All objects that belong to a specific category and for any object to which categories it belongs, which is why I can't just use joins.
My idea was to create a third table with just two columns, matching objects to categories. But it looks like an ugly solution to me and I also don't know what indices I should set upon it, if any at all. I might end up with the key column's data being saved four times: In the original table, in its PRIMARY KEY, in the matching table, and in the index on that table.
Is there a better approach or maybe even a standard method to handle such situations?
database-design sqlite
asked 1 min ago
BachsauBachsau
101
New contributor
Bachsau is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
I'm relatively new to database-design and want to create a SQLite database for an application. The application needs to handle lots of objects and assign them to a smaller list of categories. Both tables have multiple columns. The problem is, that every object may belong to an indefinite amount of categories and I need to query both efficiently: All objects that belong to a specific category and for any object to which categories it belongs, which is why I can't just use joins.
My idea was to create a third table with just two columns, matching objects to categories. But it looks like an ugly solution to me and I also don't know what indices I should set upon it, if any at all. I might end up with the key column's data being saved four times: In the original table, in its PRIMARY KEY, in the matching table, and in the index on that table.
Is there a better approach or maybe even a standard method to handle such situations?
database-design sqlite
asked 1 min ago
BachsauBachsau
101
New contributor
Bachsau is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
I'm relatively new to database-design and want to create a SQLite database for an application. The application needs to handle lots of objects and assign them to a smaller list of categories. Both tables have multiple columns. The problem is, that every object may belong to an indefinite amount of categories and I need to query both efficiently: All objects that belong to a specific category and for any object to which categories it belongs, which is why I can't just use joins.
My idea was to create a third table with just two columns, matching objects to categories. But it looks like an ugly solution to me and I also don't know what indices I should set upon it, if any at all. I might end up with the key column's data being saved four times: In the original table, in its PRIMARY KEY, in the matching table, and in the index on that table.
Is there a better approach or maybe even a standard method to handle such situations?
database-design sqlite
database-design sqlite
asked 1 min ago
BachsauBachsau
101
New contributor
Bachsau is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 1 min ago
BachsauBachsau
101
New contributor
Bachsau is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 1 min ago
BachsauBachsau
101
New contributor
Bachsau is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 1 min ago
BachsauBachsau
101
asked 1 min ago
BachsauBachsau
101
101
New contributor
Bachsau is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
New contributor
Bachsau is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Bachsau is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
add a comment |
0
active
oldest
votes
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
});
}
});
Bachsau is a new contributor. Be nice, and check out our Code of Conduct.
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%2f233713%2fhow-to-multi-match-two-tables-efficiently%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
Bachsau is a new contributor. Be nice, and check out our Code of Conduct.
Bachsau is a new contributor. Be nice, and check out our Code of Conduct.
Bachsau is a new contributor. Be nice, and check out our Code of Conduct.
Bachsau is a new contributor. Be nice, and check out our Code of Conduct.
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%2f233713%2fhow-to-multi-match-two-tables-efficiently%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
