Is using association tables for One-to-Many relationships common practice?ER Modeling; multiplicity basics -...
Why don't you get burned by the wood benches in a sauna?
Anti-aromatic and Non-aromatic compounds
Minimum Viable Product for RTS game?
Isn't a semicolon (';') needed after a function declaration in C++?
Do the speed limit reductions due to pollution also apply to electric cars in France?
Boss asked me to sign a resignation paper without a date on it along with my new contract
What are some ways of extending a description of a scenery?
Putting a vertical line in each Histogram using GraphicsGrid
Is it really OK to use "because of"?
Protagonist constantly has to have long words explained to her. Will this get tedious?
How to get a 2D Plot from a 3D Listplot?
In the Lost in Space intro why was Dr. Smith actor listed as a special guest star?
Problems formatting part entries in ToC with `titletoc`
Explicit Riemann Hilbert correspondence
Can I use a single resistor for multiple LED with different +ve sources?
Are all power cords made equal?
How to deal with an underperforming subordinate?
How unreachable are Jupiter's moons from Mars with the technology developed for going to Mars?
Can you say "leftside right"?
Can I travel from country A to country B to country C without going back to country A?
Why do single electrical receptacles exist?
Would water spill from a bowl in a Bag of Holding?
Calculating the strength of an ionic bond that contains poly-atomic ions
Why write a book when there's a movie in my head?
Is using association tables for One-to-Many relationships common practice?
ER Modeling; multiplicity basics - one to many or many to many?Nested one to many relationships?Table design for child tables with multiple many-to-many relationshipsIs this acceptable practice for a many to many?Table Relationship - One to Few (Many)Conditional relationships? One-to-many relationship using a pivot table for only a type of entityMultiple one to many relationships between tablesWhat is the best approach to write a lot of many to many relationships?How to keep history for a one-to-many relationship?A question about foreign keys and many to many relationships
I'm currently running into a conceptual disagreement with some database architects at work. My education in this field is purely from the UofGoogle so I'm sure they're right, but can't find supporting evidence online for their side.
The part of the database that we're discussing has to do with pieces of manufacturing equipment within different areas.
There is a table containing all unique AREA_CD's. Each area can contain multiple pieces of equipment (EQUIP_CD), but a single piece of equipment cannot be in multiple areas. I.e. this is a One(AREA_CD) to many(EQUIP_CD) relationship.
My understanding of db design is that we should have a table for the equipment directly related to the area table where:
AREA_CD= PK & FK to area table
EQUIP_CD= PK
(The combined PK is because there are some pieces of equipment that share the same code name, but are not the same. This is an unfortunate reality from the legacy system we are trying to upgrade).
The architects are saying that it is best practice to have the area table and equipment table joined via an association table, which I thought was specifically for many-to-many relationships.
This would make sense to me if an EQUIP_CD referred to a specific type of equipment (with attributes we're interested in storing) of which multiple could exist in different areas. However that would be a many-to-many relationship which isn't the case here. We have some shared code names that refer to different types of equipment, and the attributes depend on which area they are in.
Their justifications for the use of an association table are the following:
- Data protection. The association table is protected by the code tables.. but I don't think this adds any value in this case. There are other tables which are connected to the EQUIP_CD which will protect it.
- Future flexibility. This is understandable to me.
- Best practice. Is it though?
So my question.. Is the use of an association table for a one-to-many relationship best practice? Are there any other justifications for this design beyond the three above?
I'm not looking to challenge my colleagues, just further my learning. Thank you in advance!
oracle database-design oracle-10g
edited 7 mins ago
MDCCL
6,84331745
asked 2 days ago
DavidNDavidN
1134
New contributor
DavidN 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 currently running into a conceptual disagreement with some database architects at work. My education in this field is purely from the UofGoogle so I'm sure they're right, but can't find supporting evidence online for their side.
The part of the database that we're discussing has to do with pieces of manufacturing equipment within different areas.
There is a table containing all unique AREA_CD's. Each area can contain multiple pieces of equipment (EQUIP_CD), but a single piece of equipment cannot be in multiple areas. I.e. this is a One(AREA_CD) to many(EQUIP_CD) relationship.
My understanding of db design is that we should have a table for the equipment directly related to the area table where:
AREA_CD= PK & FK to area table
EQUIP_CD= PK
(The combined PK is because there are some pieces of equipment that share the same code name, but are not the same. This is an unfortunate reality from the legacy system we are trying to upgrade).
The architects are saying that it is best practice to have the area table and equipment table joined via an association table, which I thought was specifically for many-to-many relationships.
This would make sense to me if an EQUIP_CD referred to a specific type of equipment (with attributes we're interested in storing) of which multiple could exist in different areas. However that would be a many-to-many relationship which isn't the case here. We have some shared code names that refer to different types of equipment, and the attributes depend on which area they are in.
Their justifications for the use of an association table are the following:
- Data protection. The association table is protected by the code tables.. but I don't think this adds any value in this case. There are other tables which are connected to the EQUIP_CD which will protect it.
- Future flexibility. This is understandable to me.
- Best practice. Is it though?
So my question.. Is the use of an association table for a one-to-many relationship best practice? Are there any other justifications for this design beyond the three above?
I'm not looking to challenge my colleagues, just further my learning. Thank you in advance!
oracle database-design oracle-10g
edited 7 mins ago
MDCCL
6,84331745
asked 2 days ago
DavidNDavidN
1134
New contributor
DavidN 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 currently running into a conceptual disagreement with some database architects at work. My education in this field is purely from the UofGoogle so I'm sure they're right, but can't find supporting evidence online for their side.
The part of the database that we're discussing has to do with pieces of manufacturing equipment within different areas.
There is a table containing all unique AREA_CD's. Each area can contain multiple pieces of equipment (EQUIP_CD), but a single piece of equipment cannot be in multiple areas. I.e. this is a One(AREA_CD) to many(EQUIP_CD) relationship.
My understanding of db design is that we should have a table for the equipment directly related to the area table where:
AREA_CD= PK & FK to area table
EQUIP_CD= PK
(The combined PK is because there are some pieces of equipment that share the same code name, but are not the same. This is an unfortunate reality from the legacy system we are trying to upgrade).
The architects are saying that it is best practice to have the area table and equipment table joined via an association table, which I thought was specifically for many-to-many relationships.
This would make sense to me if an EQUIP_CD referred to a specific type of equipment (with attributes we're interested in storing) of which multiple could exist in different areas. However that would be a many-to-many relationship which isn't the case here. We have some shared code names that refer to different types of equipment, and the attributes depend on which area they are in.
Their justifications for the use of an association table are the following:
- Data protection. The association table is protected by the code tables.. but I don't think this adds any value in this case. There are other tables which are connected to the EQUIP_CD which will protect it.
- Future flexibility. This is understandable to me.
- Best practice. Is it though?
So my question.. Is the use of an association table for a one-to-many relationship best practice? Are there any other justifications for this design beyond the three above?
I'm not looking to challenge my colleagues, just further my learning. Thank you in advance!
oracle database-design oracle-10g
edited 7 mins ago
MDCCL
6,84331745
asked 2 days ago
DavidNDavidN
1134
New contributor
DavidN is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
I'm currently running into a conceptual disagreement with some database architects at work. My education in this field is purely from the UofGoogle so I'm sure they're right, but can't find supporting evidence online for their side.
The part of the database that we're discussing has to do with pieces of manufacturing equipment within different areas.
There is a table containing all unique AREA_CD's. Each area can contain multiple pieces of equipment (EQUIP_CD), but a single piece of equipment cannot be in multiple areas. I.e. this is a One(AREA_CD) to many(EQUIP_CD) relationship.
My understanding of db design is that we should have a table for the equipment directly related to the area table where:
AREA_CD= PK & FK to area table
EQUIP_CD= PK
(The combined PK is because there are some pieces of equipment that share the same code name, but are not the same. This is an unfortunate reality from the legacy system we are trying to upgrade).
The architects are saying that it is best practice to have the area table and equipment table joined via an association table, which I thought was specifically for many-to-many relationships.
This would make sense to me if an EQUIP_CD referred to a specific type of equipment (with attributes we're interested in storing) of which multiple could exist in different areas. However that would be a many-to-many relationship which isn't the case here. We have some shared code names that refer to different types of equipment, and the attributes depend on which area they are in.
Their justifications for the use of an association table are the following:
- Data protection. The association table is protected by the code tables.. but I don't think this adds any value in this case. There are other tables which are connected to the EQUIP_CD which will protect it.
- Future flexibility. This is understandable to me.
- Best practice. Is it though?
So my question.. Is the use of an association table for a one-to-many relationship best practice? Are there any other justifications for this design beyond the three above?
I'm not looking to challenge my colleagues, just further my learning. Thank you in advance!
oracle database-design oracle-10g
oracle database-design oracle-10g
edited 7 mins ago
MDCCL
6,84331745
asked 2 days ago
DavidNDavidN
1134
New contributor
DavidN is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
edited 7 mins ago
MDCCL
6,84331745
asked 2 days ago
DavidNDavidN
1134
New contributor
DavidN is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
edited 7 mins ago
MDCCL
6,84331745
edited 7 mins ago
MDCCL
6,84331745
edited 7 mins ago
MDCCL
6,84331745
6,84331745
asked 2 days ago
DavidNDavidN
1134
New contributor
DavidN is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 2 days ago
DavidNDavidN
1134
asked 2 days ago
DavidNDavidN
1134
1134
New contributor
DavidN is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
New contributor
DavidN is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
DavidN 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 |
1 Answer
1
active
oldest
votes
For the simple case you are right and there is no need to have the separate link table for a one<->many relationship.
Off the top of my head I can only think of a couple of examples where it might be a requirement to have a separate table for the relationship:
If you are tracking the history of the rows (in your business logic layer, via triggers, or "temporal tables" if Oracle supports them) and there are properties of the relationship that you don't want to track changes to in the equipment table itself. Perhaps those codes that you describe, or maybe the person currently responsible for maintaining the equipment in that location.
If you envisage needing to support a many<->many relationship later.
Though that seems rather unlikely in this case.If you have to support some oddity in a legacy framework which requires link tables for all relationships (which is plausible as you already describe needing to work around other legacy oddities).
It is simple local practise to work this way (obviously this does not make it a good idea, but it might make it one you don't have authority to fight against!)
answered yesterday
David SpillettDavid Spillett
22.6k23267
One thing that might be a consideration is how wide is the equipment table and it could be part of a design choice to limit the width of the table.
– Joe W
yesterday
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
});
}
});
DavidN 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%2f230488%2fis-using-association-tables-for-one-to-many-relationships-common-practice%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
For the simple case you are right and there is no need to have the separate link table for a one<->many relationship.
Off the top of my head I can only think of a couple of examples where it might be a requirement to have a separate table for the relationship:
If you are tracking the history of the rows (in your business logic layer, via triggers, or "temporal tables" if Oracle supports them) and there are properties of the relationship that you don't want to track changes to in the equipment table itself. Perhaps those codes that you describe, or maybe the person currently responsible for maintaining the equipment in that location.
If you envisage needing to support a many<->many relationship later.
Though that seems rather unlikely in this case.If you have to support some oddity in a legacy framework which requires link tables for all relationships (which is plausible as you already describe needing to work around other legacy oddities).
It is simple local practise to work this way (obviously this does not make it a good idea, but it might make it one you don't have authority to fight against!)
answered yesterday
David SpillettDavid Spillett
22.6k23267
One thing that might be a consideration is how wide is the equipment table and it could be part of a design choice to limit the width of the table.
– Joe W
yesterday
add a comment |
For the simple case you are right and there is no need to have the separate link table for a one<->many relationship.
Off the top of my head I can only think of a couple of examples where it might be a requirement to have a separate table for the relationship:
If you are tracking the history of the rows (in your business logic layer, via triggers, or "temporal tables" if Oracle supports them) and there are properties of the relationship that you don't want to track changes to in the equipment table itself. Perhaps those codes that you describe, or maybe the person currently responsible for maintaining the equipment in that location.
If you envisage needing to support a many<->many relationship later.
Though that seems rather unlikely in this case.If you have to support some oddity in a legacy framework which requires link tables for all relationships (which is plausible as you already describe needing to work around other legacy oddities).
It is simple local practise to work this way (obviously this does not make it a good idea, but it might make it one you don't have authority to fight against!)
answered yesterday
David SpillettDavid Spillett
22.6k23267
One thing that might be a consideration is how wide is the equipment table and it could be part of a design choice to limit the width of the table.
– Joe W
yesterday
add a comment |
For the simple case you are right and there is no need to have the separate link table for a one<->many relationship.
Off the top of my head I can only think of a couple of examples where it might be a requirement to have a separate table for the relationship:
If you are tracking the history of the rows (in your business logic layer, via triggers, or "temporal tables" if Oracle supports them) and there are properties of the relationship that you don't want to track changes to in the equipment table itself. Perhaps those codes that you describe, or maybe the person currently responsible for maintaining the equipment in that location.
If you envisage needing to support a many<->many relationship later.
Though that seems rather unlikely in this case.If you have to support some oddity in a legacy framework which requires link tables for all relationships (which is plausible as you already describe needing to work around other legacy oddities).
It is simple local practise to work this way (obviously this does not make it a good idea, but it might make it one you don't have authority to fight against!)
answered yesterday
David SpillettDavid Spillett
22.6k23267
For the simple case you are right and there is no need to have the separate link table for a one<->many relationship.
Off the top of my head I can only think of a couple of examples where it might be a requirement to have a separate table for the relationship:
If you are tracking the history of the rows (in your business logic layer, via triggers, or "temporal tables" if Oracle supports them) and there are properties of the relationship that you don't want to track changes to in the equipment table itself. Perhaps those codes that you describe, or maybe the person currently responsible for maintaining the equipment in that location.
If you envisage needing to support a many<->many relationship later.
Though that seems rather unlikely in this case.If you have to support some oddity in a legacy framework which requires link tables for all relationships (which is plausible as you already describe needing to work around other legacy oddities).
It is simple local practise to work this way (obviously this does not make it a good idea, but it might make it one you don't have authority to fight against!)
answered yesterday
David SpillettDavid Spillett
22.6k23267
answered yesterday
David SpillettDavid Spillett
22.6k23267
answered yesterday
David SpillettDavid Spillett
22.6k23267
answered yesterday
David SpillettDavid Spillett
22.6k23267
22.6k23267
One thing that might be a consideration is how wide is the equipment table and it could be part of a design choice to limit the width of the table.
– Joe W
yesterday
add a comment |
One thing that might be a consideration is how wide is the equipment table and it could be part of a design choice to limit the width of the table.
– Joe W
yesterday
One thing that might be a consideration is how wide is the equipment table and it could be part of a design choice to limit the width of the table.
– Joe W
yesterday
One thing that might be a consideration is how wide is the equipment table and it could be part of a design choice to limit the width of the table.
– Joe W
yesterday
add a comment |
DavidN is a new contributor. Be nice, and check out our Code of Conduct.
DavidN is a new contributor. Be nice, and check out our Code of Conduct.
DavidN is a new contributor. Be nice, and check out our Code of Conduct.
DavidN 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%2f230488%2fis-using-association-tables-for-one-to-many-relationships-common-practice%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
