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!
Update: The data model is being fixed, we're getting rid of those association tables.