Ygthkb

Is there a way to pause a running process on Linux systems and resume later?

How can guns be countered by melee combat without raw-ability or exceptional explanations?

Is Screenshot Time-tracking Common?

Why do single electrical receptacles exist?

Cryptic cross... with words

Does the double-bladed scimitar's special attack let you use your ability modifier for the damage of the attack?

Exploding Numbers

Coworker asking me to not bring cakes due to self control issue. What should I do?

Badly designed reimbursement form. What does that say about the company?

Excluding or including by awk

When distributing a Linux kernel driver as source code, what's the difference between Proprietary and GPL license?

SQL Server 2017 crashes when backing up because filepath is wrong

Why does this quiz question say that protons and electrons do not combine to form neutrons?

How can I give a Ranger advantage on a check due to Favored Enemy without spoiling the story for the player?

Spells that would be effective against a Modern Day army but would NOT destroy a fantasy one

How can I handle players killing my NPC outside of combat?

Why are 'and/or' operations in this Python statement behaving unexpectedly?

Can a Hydra make multiple opportunity attacks at once?

PostGIS function to move a polygon to centre over new point coordinates

Do the speed limit reductions due to pollution also apply to electric cars in France?

Can you say "leftside right"?

Including proofs of known theorems in master's thesis

Was the Soviet N1 really capable of sending 9.6 GB/s of telemetry?

Is it possible to detect 100% of SQLi with a simple regex?

Q: 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?Many one-to-many relationships between two tablesIs 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 entityStoring historical records and extracting data from a given periodMultiple one to many relationships between tablesWhat is the best approach to write a lot of many to many relationships?A question about foreign keys and many to many relationships

1

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 1(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 man-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:

1. 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.


2. Future flexibility. This is understandable to me.


3. Best practice. Is it though??

So my question.. Is the use of an association table for a 1-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 advanced!

database-design oracle-10g relational-theory

share

asked 3 mins ago

DavidNDavidN

106

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 | 

1

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 1(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 man-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:

1. 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.


2. Future flexibility. This is understandable to me.


3. Best practice. Is it though??

So my question.. Is the use of an association table for a 1-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 advanced!

database-design oracle-10g relational-theory

share

asked 3 mins ago

DavidNDavidN

106

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 1(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 man-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:

1. 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.


2. Future flexibility. This is understandable to me.


3. Best practice. Is it though??

So my question.. Is the use of an association table for a 1-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 advanced!

database-design oracle-10g relational-theory

share

asked 3 mins ago

DavidNDavidN

106

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.

share

asked 3 mins ago

DavidNDavidN

106

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.

share

asked 3 mins ago

DavidNDavidN

106

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 3 mins ago

DavidNDavidN

106

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 3 mins ago

DavidNDavidN

106