Two ways to define may-have-a (1..0-1) relationshipA separate table or ENUM?The nature of the relationship...
Does an increasing sequence of reals converge if the difference of consecutive terms approaches zero?
Do error bars on probabilities have any meaning?
Is layered encryption more secure than long passwords?
How can I differentiate duration vs starting time
How to display entire MAC address table on HP switches
Define function that behaves almost identically to Mathematica function
Is it possible to detect 100% of SQLi with a simple regex?
How to play song that contains one guitar when we have two guitarists (or more)?
How do I add a strong "onion flavor" to the biryani (in restaurant style)?
Would plants on a planet orbiting a star like ours, have any different color?
boss asked me to sign a resignation paper without a date on it along with my new contract
Is it ethical to apply for a job on someone's behalf?
Have any astronauts or cosmonauts died in space?
Why is Bernie Sanders maximum accepted donation on actblue $5600?
Why Third 'Reich'? Why is 'reich' not translated when 'third' is? What is the English synonym of reich?
Which was the first story to feature space elevators?
Why is Shelob considered evil?
How bad is a Computer Science course that doesn't teach Design Patterns?
Are encryption algorithms with fixed-point free permutations inherently flawed?
Can I reorder the coordinates of a line when importing into QGIS a WKT linestring?
Electricity for magnetic shield at Mars L1 Lagrange point
Discouraging missile alpha strikes
Failing PhD, how to go forward?
Why does RAM (any type) access time decrease so slowly?
Two ways to define may-have-a (1..0-1) relationship
A separate table or ENUM?The nature of the relationship between two tables in MySQLForeign key for rows added laterSpotting foreign key relations in a database that doesn't define all relationships as constraints?Relation between tablesMany-to-many relationship, what's the best choice for primary key?Phpmyadmin / mysql error defining primary keys as foreign keys of two separate tablesAvoiding foreign key repetition on related models in a relational DBHow to handle variations of the same data point?MS Access Foreign key not working in SQL Server
Today I have discovered for myself that may-have-a (1-0..1) relation can be implemented in two different ways.
Let A be the "main" table (left side of ..) and B be the "linked" table (right side of ..).
The table A has a field which can be either a number of NULL. The number links to the primary key of B.
The table B has a UNIQUE field which links to a primary key in A.
Mathematically these two ways to link tables are equivalent (they are both 1-0..1 that is may-have-a), but the structure of tables is different for these two variants.
I am writing a lightweight ORM for our DB. (I know I should not, but so things work in our company.)
The bad thing is that we have may-have-a relations in our database implemented sometimes in one way, sometimes in the other.
Should we restructure our DB to retain only one of these two relations? Which of the two variants to retain?
As a temporary measurement, I propose to create in my ORM two relationships: may-have-a1 and may-have-a2. Is there any way to avoid this trickery?
MySQL.
foreign-key relational-theory unique-constraint
asked Nov 3 '15 at 16:28
portonporton
3241419
add a comment |
Today I have discovered for myself that may-have-a (1-0..1) relation can be implemented in two different ways.
Let A be the "main" table (left side of ..) and B be the "linked" table (right side of ..).
The table A has a field which can be either a number of NULL. The number links to the primary key of B.
The table B has a UNIQUE field which links to a primary key in A.
Mathematically these two ways to link tables are equivalent (they are both 1-0..1 that is may-have-a), but the structure of tables is different for these two variants.
I am writing a lightweight ORM for our DB. (I know I should not, but so things work in our company.)
The bad thing is that we have may-have-a relations in our database implemented sometimes in one way, sometimes in the other.
Should we restructure our DB to retain only one of these two relations? Which of the two variants to retain?
As a temporary measurement, I propose to create in my ORM two relationships: may-have-a1 and may-have-a2. Is there any way to avoid this trickery?
MySQL.
foreign-key relational-theory unique-constraint
asked Nov 3 '15 at 16:28
portonporton
3241419
Maybe I'm not really following your relationship here. Usually when you have a 0-1 to 1 relationship, you do one of two things: 1) Put the 0-1 fields in the 1 table and allow them to be nulls. This is denormalized, but practical. 2) Create a second table for the 0-1 fields and use the exact same primary key as the 1 table. This is normalized, but complex. I can't envision a situation where you'd need to create a key for the 0-1 table that you'd then store in the 1 table but allow to be null. That seems like just the worst of both worlds.
– Bacon Bits
Nov 3 '15 at 16:40
5
Your design (2) is truly a1 - 0..1relationship. the design (1) is a messup, more like a0..1 - 0..1relationship (assuming this "number or null" column has a Unique constraint, too. If not, it's a0..n - 0..1.)
– ypercubeᵀᴹ
Nov 4 '15 at 7:44
Generally the second option is preferable because deletion from B doesn't require updates of A.
– Serg
Jun 27 '16 at 8:39
One benefit to option 1 is that if no B is related to it, it does not need to perform a B-lookup which finds nothing. In my case, most A's do not link to B's. And table A is so huge that it's a pain (slow on mysql 5.5, production downtime) to add/remove columns. And table B may have more frequent columns changes in the future. However, Option 2 does seem cleaner and standard.
– Curtis Yallop
Feb 8 at 21:59
add a comment |
Today I have discovered for myself that may-have-a (1-0..1) relation can be implemented in two different ways.
Let A be the "main" table (left side of ..) and B be the "linked" table (right side of ..).
The table A has a field which can be either a number of NULL. The number links to the primary key of B.
The table B has a UNIQUE field which links to a primary key in A.
Mathematically these two ways to link tables are equivalent (they are both 1-0..1 that is may-have-a), but the structure of tables is different for these two variants.
I am writing a lightweight ORM for our DB. (I know I should not, but so things work in our company.)
The bad thing is that we have may-have-a relations in our database implemented sometimes in one way, sometimes in the other.
Should we restructure our DB to retain only one of these two relations? Which of the two variants to retain?
As a temporary measurement, I propose to create in my ORM two relationships: may-have-a1 and may-have-a2. Is there any way to avoid this trickery?
MySQL.
foreign-key relational-theory unique-constraint
asked Nov 3 '15 at 16:28
portonporton
3241419
Today I have discovered for myself that may-have-a (1-0..1) relation can be implemented in two different ways.
Let A be the "main" table (left side of ..) and B be the "linked" table (right side of ..).
The table A has a field which can be either a number of NULL. The number links to the primary key of B.
The table B has a UNIQUE field which links to a primary key in A.
Mathematically these two ways to link tables are equivalent (they are both 1-0..1 that is may-have-a), but the structure of tables is different for these two variants.
I am writing a lightweight ORM for our DB. (I know I should not, but so things work in our company.)
The bad thing is that we have may-have-a relations in our database implemented sometimes in one way, sometimes in the other.
Should we restructure our DB to retain only one of these two relations? Which of the two variants to retain?
As a temporary measurement, I propose to create in my ORM two relationships: may-have-a1 and may-have-a2. Is there any way to avoid this trickery?
MySQL.
foreign-key relational-theory unique-constraint
foreign-key relational-theory unique-constraint
asked Nov 3 '15 at 16:28
portonporton
3241419
asked Nov 3 '15 at 16:28
portonporton
3241419
asked Nov 3 '15 at 16:28
portonporton
3241419
asked Nov 3 '15 at 16:28
portonporton
3241419
asked Nov 3 '15 at 16:28
portonporton
3241419
3241419
Maybe I'm not really following your relationship here. Usually when you have a 0-1 to 1 relationship, you do one of two things: 1) Put the 0-1 fields in the 1 table and allow them to be nulls. This is denormalized, but practical. 2) Create a second table for the 0-1 fields and use the exact same primary key as the 1 table. This is normalized, but complex. I can't envision a situation where you'd need to create a key for the 0-1 table that you'd then store in the 1 table but allow to be null. That seems like just the worst of both worlds.
– Bacon Bits
Nov 3 '15 at 16:40
5
Your design (2) is truly a1 - 0..1relationship. the design (1) is a messup, more like a0..1 - 0..1relationship (assuming this "number or null" column has a Unique constraint, too. If not, it's a0..n - 0..1.)
– ypercubeᵀᴹ
Nov 4 '15 at 7:44
Generally the second option is preferable because deletion from B doesn't require updates of A.
– Serg
Jun 27 '16 at 8:39
One benefit to option 1 is that if no B is related to it, it does not need to perform a B-lookup which finds nothing. In my case, most A's do not link to B's. And table A is so huge that it's a pain (slow on mysql 5.5, production downtime) to add/remove columns. And table B may have more frequent columns changes in the future. However, Option 2 does seem cleaner and standard.
– Curtis Yallop
Feb 8 at 21:59
add a comment |
Maybe I'm not really following your relationship here. Usually when you have a 0-1 to 1 relationship, you do one of two things: 1) Put the 0-1 fields in the 1 table and allow them to be nulls. This is denormalized, but practical. 2) Create a second table for the 0-1 fields and use the exact same primary key as the 1 table. This is normalized, but complex. I can't envision a situation where you'd need to create a key for the 0-1 table that you'd then store in the 1 table but allow to be null. That seems like just the worst of both worlds.
– Bacon Bits
Nov 3 '15 at 16:40
5
Your design (2) is truly a1 - 0..1relationship. the design (1) is a messup, more like a0..1 - 0..1relationship (assuming this "number or null" column has a Unique constraint, too. If not, it's a0..n - 0..1.)
– ypercubeᵀᴹ
Nov 4 '15 at 7:44
Generally the second option is preferable because deletion from B doesn't require updates of A.
– Serg
Jun 27 '16 at 8:39
One benefit to option 1 is that if no B is related to it, it does not need to perform a B-lookup which finds nothing. In my case, most A's do not link to B's. And table A is so huge that it's a pain (slow on mysql 5.5, production downtime) to add/remove columns. And table B may have more frequent columns changes in the future. However, Option 2 does seem cleaner and standard.
– Curtis Yallop
Feb 8 at 21:59
Maybe I'm not really following your relationship here. Usually when you have a 0-1 to 1 relationship, you do one of two things: 1) Put the 0-1 fields in the 1 table and allow them to be nulls. This is denormalized, but practical. 2) Create a second table for the 0-1 fields and use the exact same primary key as the 1 table. This is normalized, but complex. I can't envision a situation where you'd need to create a key for the 0-1 table that you'd then store in the 1 table but allow to be null. That seems like just the worst of both worlds.
– Bacon Bits
Nov 3 '15 at 16:40
Maybe I'm not really following your relationship here. Usually when you have a 0-1 to 1 relationship, you do one of two things: 1) Put the 0-1 fields in the 1 table and allow them to be nulls. This is denormalized, but practical. 2) Create a second table for the 0-1 fields and use the exact same primary key as the 1 table. This is normalized, but complex. I can't envision a situation where you'd need to create a key for the 0-1 table that you'd then store in the 1 table but allow to be null. That seems like just the worst of both worlds.
– Bacon Bits
Nov 3 '15 at 16:40
5
5
Your design (2) is truly a
1 - 0..1 relationship. the design (1) is a messup, more like a 0..1 - 0..1 relationship (assuming this "number or null" column has a Unique constraint, too. If not, it's a 0..n - 0..1.)– ypercubeᵀᴹ
Nov 4 '15 at 7:44
Your design (2) is truly a
1 - 0..1 relationship. the design (1) is a messup, more like a 0..1 - 0..1 relationship (assuming this "number or null" column has a Unique constraint, too. If not, it's a 0..n - 0..1.)– ypercubeᵀᴹ
Nov 4 '15 at 7:44
Generally the second option is preferable because deletion from B doesn't require updates of A.
– Serg
Jun 27 '16 at 8:39
Generally the second option is preferable because deletion from B doesn't require updates of A.
– Serg
Jun 27 '16 at 8:39
One benefit to option 1 is that if no B is related to it, it does not need to perform a B-lookup which finds nothing. In my case, most A's do not link to B's. And table A is so huge that it's a pain (slow on mysql 5.5, production downtime) to add/remove columns. And table B may have more frequent columns changes in the future. However, Option 2 does seem cleaner and standard.
– Curtis Yallop
Feb 8 at 21:59
One benefit to option 1 is that if no B is related to it, it does not need to perform a B-lookup which finds nothing. In my case, most A's do not link to B's. And table A is so huge that it's a pain (slow on mysql 5.5, production downtime) to add/remove columns. And table B may have more frequent columns changes in the future. However, Option 2 does seem cleaner and standard.
– Curtis Yallop
Feb 8 at 21:59
add a comment |
2 Answers
2
active
oldest
votes
You've got 2 conflicting design goals here. One is to design a good database. The other is to play nicely with OR mappers. You might be able to shift to a more consistent approach to mapping partial participation constraints, but you will continue to have design conflicts in the future.
One option would be to create a set of views to worm with your ORM, and so abstract the problem away.
answered Nov 3 '15 at 22:09
beldazbeldaz
1,000821
add a comment |
Both ways can be valid depending on the scenario.
Method 2 is the more standard, common way. It is a 1-N relationship only N is either 0 or 1. FK columns are by default indexed in most databases and go on the table on the N side (just like you describe with your unique index only an additional FK constraint fails if you insert an FK-column-value which does not match any PKs in A).
Method 1 follows the same structure as an N-1 relationship (FK column is on the N side). It can be done for example in hibernate using a ManyToOne relationship where optional=true. It is an N-to-0/1 relationship where N happens to be always 1.
Method 1 can make complete sense. Say A is "Client" and B is "SalesContact". Client will only have 0 or 1 SalesContact. Perhaps each SalesContact is only assigned to 1 Client. But we want to support SalesContact being assigned to multiple Clients in the future.
Method 2 can make complete sense. Say you have "Client" and "Address". An Client can have 0 or 1 Address. But in the future, we want to support clients having multiple addresses.
But if you know you will always have 1-to-0/1 and will never want 1-N or N-1 in the future, you can choose either way. Method 2 is probably a better choice.
Most ORMs do not have a 1-to-0/1 construct specifically, just 1-N, N-1 and maybe 1-1. In hibernate, the 1 side of 1-N/N-1 can be 0 for optional=true.
Further note: I am currently considering Method 1 for efficiency reasons. But I notice at least in mysql that SELECT * WHERE key = NULL (not IS NULL, outright null-lookup-in-index similar to key = :inserted_value with inserted_value as null), EXPLAIN is indicating an index lookup anyways! You might think that if the key NULL is being looked up in the index, the db would just skip the lookup and return nothing (maybe it still does that, I'm not sure). Not only that but when I log generated SQL from hibernate, using ManyToOne + optional=true + Eager it always does a left join - when I look up either 1 or multiple A's. When I switch to optional=false (N-1 not N-to-0/1), it changes the left join to an inner join. So in my case, OneToMany and ManyToOne are both table joins. It is possible that the db engine is still faster on ManyToOne but only if it starts with the A table and is smart enough to skip the null lookup on B. Note that whether the db engine starts with A or B is up to the db engine query optimization logic but for certain queries it will always choose A.
answered 4 mins ago
Curtis YallopCurtis Yallop
1012
New contributor
Curtis Yallop 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 |
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%2f119967%2ftwo-ways-to-define-may-have-a-1-0-1-relationship%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
You've got 2 conflicting design goals here. One is to design a good database. The other is to play nicely with OR mappers. You might be able to shift to a more consistent approach to mapping partial participation constraints, but you will continue to have design conflicts in the future.
One option would be to create a set of views to worm with your ORM, and so abstract the problem away.
answered Nov 3 '15 at 22:09
beldazbeldaz
1,000821
add a comment |
You've got 2 conflicting design goals here. One is to design a good database. The other is to play nicely with OR mappers. You might be able to shift to a more consistent approach to mapping partial participation constraints, but you will continue to have design conflicts in the future.
One option would be to create a set of views to worm with your ORM, and so abstract the problem away.
answered Nov 3 '15 at 22:09
beldazbeldaz
1,000821
add a comment |
You've got 2 conflicting design goals here. One is to design a good database. The other is to play nicely with OR mappers. You might be able to shift to a more consistent approach to mapping partial participation constraints, but you will continue to have design conflicts in the future.
One option would be to create a set of views to worm with your ORM, and so abstract the problem away.
answered Nov 3 '15 at 22:09
beldazbeldaz
1,000821
You've got 2 conflicting design goals here. One is to design a good database. The other is to play nicely with OR mappers. You might be able to shift to a more consistent approach to mapping partial participation constraints, but you will continue to have design conflicts in the future.
One option would be to create a set of views to worm with your ORM, and so abstract the problem away.
answered Nov 3 '15 at 22:09
beldazbeldaz
1,000821
answered Nov 3 '15 at 22:09
beldazbeldaz
1,000821
answered Nov 3 '15 at 22:09
beldazbeldaz
1,000821
answered Nov 3 '15 at 22:09
beldazbeldaz
1,000821
1,000821
add a comment |
add a comment |
Both ways can be valid depending on the scenario.
Method 2 is the more standard, common way. It is a 1-N relationship only N is either 0 or 1. FK columns are by default indexed in most databases and go on the table on the N side (just like you describe with your unique index only an additional FK constraint fails if you insert an FK-column-value which does not match any PKs in A).
Method 1 follows the same structure as an N-1 relationship (FK column is on the N side). It can be done for example in hibernate using a ManyToOne relationship where optional=true. It is an N-to-0/1 relationship where N happens to be always 1.
Method 1 can make complete sense. Say A is "Client" and B is "SalesContact". Client will only have 0 or 1 SalesContact. Perhaps each SalesContact is only assigned to 1 Client. But we want to support SalesContact being assigned to multiple Clients in the future.
Method 2 can make complete sense. Say you have "Client" and "Address". An Client can have 0 or 1 Address. But in the future, we want to support clients having multiple addresses.
But if you know you will always have 1-to-0/1 and will never want 1-N or N-1 in the future, you can choose either way. Method 2 is probably a better choice.
Most ORMs do not have a 1-to-0/1 construct specifically, just 1-N, N-1 and maybe 1-1. In hibernate, the 1 side of 1-N/N-1 can be 0 for optional=true.
Further note: I am currently considering Method 1 for efficiency reasons. But I notice at least in mysql that SELECT * WHERE key = NULL (not IS NULL, outright null-lookup-in-index similar to key = :inserted_value with inserted_value as null), EXPLAIN is indicating an index lookup anyways! You might think that if the key NULL is being looked up in the index, the db would just skip the lookup and return nothing (maybe it still does that, I'm not sure). Not only that but when I log generated SQL from hibernate, using ManyToOne + optional=true + Eager it always does a left join - when I look up either 1 or multiple A's. When I switch to optional=false (N-1 not N-to-0/1), it changes the left join to an inner join. So in my case, OneToMany and ManyToOne are both table joins. It is possible that the db engine is still faster on ManyToOne but only if it starts with the A table and is smart enough to skip the null lookup on B. Note that whether the db engine starts with A or B is up to the db engine query optimization logic but for certain queries it will always choose A.
answered 4 mins ago
Curtis YallopCurtis Yallop
1012
New contributor
Curtis Yallop 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 |
Both ways can be valid depending on the scenario.
Method 2 is the more standard, common way. It is a 1-N relationship only N is either 0 or 1. FK columns are by default indexed in most databases and go on the table on the N side (just like you describe with your unique index only an additional FK constraint fails if you insert an FK-column-value which does not match any PKs in A).
Method 1 follows the same structure as an N-1 relationship (FK column is on the N side). It can be done for example in hibernate using a ManyToOne relationship where optional=true. It is an N-to-0/1 relationship where N happens to be always 1.
Method 1 can make complete sense. Say A is "Client" and B is "SalesContact". Client will only have 0 or 1 SalesContact. Perhaps each SalesContact is only assigned to 1 Client. But we want to support SalesContact being assigned to multiple Clients in the future.
Method 2 can make complete sense. Say you have "Client" and "Address". An Client can have 0 or 1 Address. But in the future, we want to support clients having multiple addresses.
But if you know you will always have 1-to-0/1 and will never want 1-N or N-1 in the future, you can choose either way. Method 2 is probably a better choice.
Most ORMs do not have a 1-to-0/1 construct specifically, just 1-N, N-1 and maybe 1-1. In hibernate, the 1 side of 1-N/N-1 can be 0 for optional=true.
Further note: I am currently considering Method 1 for efficiency reasons. But I notice at least in mysql that SELECT * WHERE key = NULL (not IS NULL, outright null-lookup-in-index similar to key = :inserted_value with inserted_value as null), EXPLAIN is indicating an index lookup anyways! You might think that if the key NULL is being looked up in the index, the db would just skip the lookup and return nothing (maybe it still does that, I'm not sure). Not only that but when I log generated SQL from hibernate, using ManyToOne + optional=true + Eager it always does a left join - when I look up either 1 or multiple A's. When I switch to optional=false (N-1 not N-to-0/1), it changes the left join to an inner join. So in my case, OneToMany and ManyToOne are both table joins. It is possible that the db engine is still faster on ManyToOne but only if it starts with the A table and is smart enough to skip the null lookup on B. Note that whether the db engine starts with A or B is up to the db engine query optimization logic but for certain queries it will always choose A.
answered 4 mins ago
Curtis YallopCurtis Yallop
1012
New contributor
Curtis Yallop 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 |
Both ways can be valid depending on the scenario.
Method 2 is the more standard, common way. It is a 1-N relationship only N is either 0 or 1. FK columns are by default indexed in most databases and go on the table on the N side (just like you describe with your unique index only an additional FK constraint fails if you insert an FK-column-value which does not match any PKs in A).
Method 1 follows the same structure as an N-1 relationship (FK column is on the N side). It can be done for example in hibernate using a ManyToOne relationship where optional=true. It is an N-to-0/1 relationship where N happens to be always 1.
Method 1 can make complete sense. Say A is "Client" and B is "SalesContact". Client will only have 0 or 1 SalesContact. Perhaps each SalesContact is only assigned to 1 Client. But we want to support SalesContact being assigned to multiple Clients in the future.
Method 2 can make complete sense. Say you have "Client" and "Address". An Client can have 0 or 1 Address. But in the future, we want to support clients having multiple addresses.
But if you know you will always have 1-to-0/1 and will never want 1-N or N-1 in the future, you can choose either way. Method 2 is probably a better choice.
Most ORMs do not have a 1-to-0/1 construct specifically, just 1-N, N-1 and maybe 1-1. In hibernate, the 1 side of 1-N/N-1 can be 0 for optional=true.
Further note: I am currently considering Method 1 for efficiency reasons. But I notice at least in mysql that SELECT * WHERE key = NULL (not IS NULL, outright null-lookup-in-index similar to key = :inserted_value with inserted_value as null), EXPLAIN is indicating an index lookup anyways! You might think that if the key NULL is being looked up in the index, the db would just skip the lookup and return nothing (maybe it still does that, I'm not sure). Not only that but when I log generated SQL from hibernate, using ManyToOne + optional=true + Eager it always does a left join - when I look up either 1 or multiple A's. When I switch to optional=false (N-1 not N-to-0/1), it changes the left join to an inner join. So in my case, OneToMany and ManyToOne are both table joins. It is possible that the db engine is still faster on ManyToOne but only if it starts with the A table and is smart enough to skip the null lookup on B. Note that whether the db engine starts with A or B is up to the db engine query optimization logic but for certain queries it will always choose A.
answered 4 mins ago
Curtis YallopCurtis Yallop
1012
New contributor
Curtis Yallop is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Both ways can be valid depending on the scenario.
Method 2 is the more standard, common way. It is a 1-N relationship only N is either 0 or 1. FK columns are by default indexed in most databases and go on the table on the N side (just like you describe with your unique index only an additional FK constraint fails if you insert an FK-column-value which does not match any PKs in A).
Method 1 follows the same structure as an N-1 relationship (FK column is on the N side). It can be done for example in hibernate using a ManyToOne relationship where optional=true. It is an N-to-0/1 relationship where N happens to be always 1.
Method 1 can make complete sense. Say A is "Client" and B is "SalesContact". Client will only have 0 or 1 SalesContact. Perhaps each SalesContact is only assigned to 1 Client. But we want to support SalesContact being assigned to multiple Clients in the future.
Method 2 can make complete sense. Say you have "Client" and "Address". An Client can have 0 or 1 Address. But in the future, we want to support clients having multiple addresses.
But if you know you will always have 1-to-0/1 and will never want 1-N or N-1 in the future, you can choose either way. Method 2 is probably a better choice.
Most ORMs do not have a 1-to-0/1 construct specifically, just 1-N, N-1 and maybe 1-1. In hibernate, the 1 side of 1-N/N-1 can be 0 for optional=true.
Further note: I am currently considering Method 1 for efficiency reasons. But I notice at least in mysql that SELECT * WHERE key = NULL (not IS NULL, outright null-lookup-in-index similar to key = :inserted_value with inserted_value as null), EXPLAIN is indicating an index lookup anyways! You might think that if the key NULL is being looked up in the index, the db would just skip the lookup and return nothing (maybe it still does that, I'm not sure). Not only that but when I log generated SQL from hibernate, using ManyToOne + optional=true + Eager it always does a left join - when I look up either 1 or multiple A's. When I switch to optional=false (N-1 not N-to-0/1), it changes the left join to an inner join. So in my case, OneToMany and ManyToOne are both table joins. It is possible that the db engine is still faster on ManyToOne but only if it starts with the A table and is smart enough to skip the null lookup on B. Note that whether the db engine starts with A or B is up to the db engine query optimization logic but for certain queries it will always choose A.
answered 4 mins ago
Curtis YallopCurtis Yallop
1012
New contributor
Curtis Yallop is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
answered 4 mins ago
Curtis YallopCurtis Yallop
1012
New contributor
Curtis Yallop is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
answered 4 mins ago
Curtis YallopCurtis Yallop
1012
answered 4 mins ago
Curtis YallopCurtis Yallop
1012
1012
New contributor
Curtis Yallop is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
New contributor
Curtis Yallop is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Curtis Yallop 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 |
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%2f119967%2ftwo-ways-to-define-may-have-a-1-0-1-relationship%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
Maybe I'm not really following your relationship here. Usually when you have a 0-1 to 1 relationship, you do one of two things: 1) Put the 0-1 fields in the 1 table and allow them to be nulls. This is denormalized, but practical. 2) Create a second table for the 0-1 fields and use the exact same primary key as the 1 table. This is normalized, but complex. I can't envision a situation where you'd need to create a key for the 0-1 table that you'd then store in the 1 table but allow to be null. That seems like just the worst of both worlds.
– Bacon Bits
Nov 3 '15 at 16:40
5
Your design (2) is truly a
1 - 0..1relationship. the design (1) is a messup, more like a0..1 - 0..1relationship (assuming this "number or null" column has a Unique constraint, too. If not, it's a0..n - 0..1.)– ypercubeᵀᴹ
Nov 4 '15 at 7:44
Generally the second option is preferable because deletion from B doesn't require updates of A.
– Serg
Jun 27 '16 at 8:39
One benefit to option 1 is that if no B is related to it, it does not need to perform a B-lookup which finds nothing. In my case, most A's do not link to B's. And table A is so huge that it's a pain (slow on mysql 5.5, production downtime) to add/remove columns. And table B may have more frequent columns changes in the future. However, Option 2 does seem cleaner and standard.
– Curtis Yallop
Feb 8 at 21:59