Ygthkb

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:

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 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!

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!)