Polymorphic Associations in SQL
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
I'm creating a project with big database of Movies and Series, both in seperate tables. Now I have other tables like Country for specifying production country of movie or/and serie. If I want to do this in many to many relation it requires connection table one for movie and one for serie. It would look like this:
DB with separate connection tables.
It is many tables, so I was searching for other solutions and I found this article and the "Using one data entity per class" method seems to be best for me. I implemented it like that: DB with combined connection table.
The second implementation seems to be good, but there's a one problem I'm facing and that's a complicated inserts. To insert new Movie, I need first to add new Production and second to insert Movie and pick just created Production ID.
My question is could it be fixed in some way ? Can't it be auto incremented from Movie table ?
I'm using 10.1.36-MariaDB and I'm realy sorry for my poor english :c
sql polymorphism mariadb associations
asked Nov 26 '18 at 17:35
SzwajowySzwajowy
53
add a comment |
I'm creating a project with big database of Movies and Series, both in seperate tables. Now I have other tables like Country for specifying production country of movie or/and serie. If I want to do this in many to many relation it requires connection table one for movie and one for serie. It would look like this:
DB with separate connection tables.
It is many tables, so I was searching for other solutions and I found this article and the "Using one data entity per class" method seems to be best for me. I implemented it like that: DB with combined connection table.
The second implementation seems to be good, but there's a one problem I'm facing and that's a complicated inserts. To insert new Movie, I need first to add new Production and second to insert Movie and pick just created Production ID.
My question is could it be fixed in some way ? Can't it be auto incremented from Movie table ?
I'm using 10.1.36-MariaDB and I'm realy sorry for my poor english :c
sql polymorphism mariadb associations
asked Nov 26 '18 at 17:35
SzwajowySzwajowy
53
add a comment |
I'm creating a project with big database of Movies and Series, both in seperate tables. Now I have other tables like Country for specifying production country of movie or/and serie. If I want to do this in many to many relation it requires connection table one for movie and one for serie. It would look like this:
DB with separate connection tables.
It is many tables, so I was searching for other solutions and I found this article and the "Using one data entity per class" method seems to be best for me. I implemented it like that: DB with combined connection table.
The second implementation seems to be good, but there's a one problem I'm facing and that's a complicated inserts. To insert new Movie, I need first to add new Production and second to insert Movie and pick just created Production ID.
My question is could it be fixed in some way ? Can't it be auto incremented from Movie table ?
I'm using 10.1.36-MariaDB and I'm realy sorry for my poor english :c
sql polymorphism mariadb associations
asked Nov 26 '18 at 17:35
SzwajowySzwajowy
53
I'm creating a project with big database of Movies and Series, both in seperate tables. Now I have other tables like Country for specifying production country of movie or/and serie. If I want to do this in many to many relation it requires connection table one for movie and one for serie. It would look like this:
DB with separate connection tables.
It is many tables, so I was searching for other solutions and I found this article and the "Using one data entity per class" method seems to be best for me. I implemented it like that: DB with combined connection table.
The second implementation seems to be good, but there's a one problem I'm facing and that's a complicated inserts. To insert new Movie, I need first to add new Production and second to insert Movie and pick just created Production ID.
My question is could it be fixed in some way ? Can't it be auto incremented from Movie table ?
I'm using 10.1.36-MariaDB and I'm realy sorry for my poor english :c
sql polymorphism mariadb associations
sql polymorphism mariadb associations
asked Nov 26 '18 at 17:35
SzwajowySzwajowy
53
asked Nov 26 '18 at 17:35
SzwajowySzwajowy
53
asked Nov 26 '18 at 17:35
SzwajowySzwajowy
53
asked Nov 26 '18 at 17:35
SzwajowySzwajowy
53
asked Nov 26 '18 at 17:35
SzwajowySzwajowy
53
53
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
Since Movies and Series have only one column that is different (boxoffice), it makes sense to put the two tables together and let boxoffice be NULL for series. I don't understand the need for Production, but it seems to add as much complexity as it saves. Try to get rid of it.
I suggest that the id for Country be the standard 2-letter codes. This will be more compact and eliminate some stuff.
The is no good reason to have an id for a connection table. See many-to-many tips . Those tips will speed up many of your queries, and save space.
"Polymorphic" and many other neat-things-in-a-textbook don't necessarily work well in Relational Databases.
answered Nov 27 '18 at 2:06
Rick JamesRick James
70.7k566106
That's great answer. Thank you for your help :)
– Szwajowy
Nov 29 '18 at 19:39
add a comment |
Your Answer
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: "1"
};
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: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
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%2fstackoverflow.com%2fquestions%2f53486295%2fpolymorphic-associations-in-sql%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
Since Movies and Series have only one column that is different (boxoffice), it makes sense to put the two tables together and let boxoffice be NULL for series. I don't understand the need for Production, but it seems to add as much complexity as it saves. Try to get rid of it.
I suggest that the id for Country be the standard 2-letter codes. This will be more compact and eliminate some stuff.
The is no good reason to have an id for a connection table. See many-to-many tips . Those tips will speed up many of your queries, and save space.
"Polymorphic" and many other neat-things-in-a-textbook don't necessarily work well in Relational Databases.
answered Nov 27 '18 at 2:06
Rick JamesRick James
70.7k566106
That's great answer. Thank you for your help :)
– Szwajowy
Nov 29 '18 at 19:39
add a comment |
Since Movies and Series have only one column that is different (boxoffice), it makes sense to put the two tables together and let boxoffice be NULL for series. I don't understand the need for Production, but it seems to add as much complexity as it saves. Try to get rid of it.
I suggest that the id for Country be the standard 2-letter codes. This will be more compact and eliminate some stuff.
The is no good reason to have an id for a connection table. See many-to-many tips . Those tips will speed up many of your queries, and save space.
"Polymorphic" and many other neat-things-in-a-textbook don't necessarily work well in Relational Databases.
answered Nov 27 '18 at 2:06
Rick JamesRick James
70.7k566106
That's great answer. Thank you for your help :)
– Szwajowy
Nov 29 '18 at 19:39
add a comment |
Since Movies and Series have only one column that is different (boxoffice), it makes sense to put the two tables together and let boxoffice be NULL for series. I don't understand the need for Production, but it seems to add as much complexity as it saves. Try to get rid of it.
I suggest that the id for Country be the standard 2-letter codes. This will be more compact and eliminate some stuff.
The is no good reason to have an id for a connection table. See many-to-many tips . Those tips will speed up many of your queries, and save space.
"Polymorphic" and many other neat-things-in-a-textbook don't necessarily work well in Relational Databases.
answered Nov 27 '18 at 2:06
Rick JamesRick James
70.7k566106
Since Movies and Series have only one column that is different (boxoffice), it makes sense to put the two tables together and let boxoffice be NULL for series. I don't understand the need for Production, but it seems to add as much complexity as it saves. Try to get rid of it.
I suggest that the id for Country be the standard 2-letter codes. This will be more compact and eliminate some stuff.
The is no good reason to have an id for a connection table. See many-to-many tips . Those tips will speed up many of your queries, and save space.
"Polymorphic" and many other neat-things-in-a-textbook don't necessarily work well in Relational Databases.
answered Nov 27 '18 at 2:06
Rick JamesRick James
70.7k566106
edited Nov 29 '18 at 20:47
answered Nov 27 '18 at 2:06
Rick JamesRick James
70.7k566106
answered Nov 27 '18 at 2:06
Rick JamesRick James
70.7k566106
answered Nov 27 '18 at 2:06
Rick JamesRick James
70.7k566106
70.7k566106
That's great answer. Thank you for your help :)
– Szwajowy
Nov 29 '18 at 19:39
add a comment |
That's great answer. Thank you for your help :)
– Szwajowy
Nov 29 '18 at 19:39
That's great answer. Thank you for your help :)
– Szwajowy
Nov 29 '18 at 19:39
That's great answer. Thank you for your help :)
– Szwajowy
Nov 29 '18 at 19:39
add a comment |
Thanks for contributing an answer to Stack Overflow!
- 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%2fstackoverflow.com%2fquestions%2f53486295%2fpolymorphic-associations-in-sql%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
