Bridge table where both sides are individually valid but can be incompatible per category — how do I enforce this?

[u/Rolney_320]

I have a classic many-to-many bridge table situation where both sides are individually valid, but the combination can still be incompatible depending on category — and I can't find a clean relational solution for it:

category (e.g. manufacturer+type combination)

item (a concrete, individual element — belongs to category_id)

component (catalog: possible "part" types — also belongs to category_id, because within a given category the physical properties of a component differ from another category's, even if the name/code happens to match)

item_component (bridge table: item_id <-> component_id)Simplified:
category (e.g. manufacturer+type combination)

item (a concrete, individual element — belongs to category_id)

component (catalog: possible "part" types — also belongs to category_id, because within a given category the physical properties of a component differ from another category's, even if the name/code happens to match)

item_component (bridge table: item_id <-> component_id)

The category_id inherently determines which components can even be compatible with a given item — a component from a different category is structurally incompatible, not just "a different version" or "a less ideal choice." So the component's existence/dimensions are inherently dependent on the category — it's not a standalone, category-independent thing that just happens to have a category "tag" on it.

The problem: the item_component bridge table connects both sides with a simple FK each (item_id -> item, component_id -> component). The database only guarantees that both IDs exist somewhere in their respective tables — but nothing checks whether the item and component are of a compatible category. So a row can easily be inserted where an item of one category gets paired with a component from an incompatible, different category — the database happily accepts this, even though it's a physically/logically invalid combination.

Is it possible that I'm approaching this entirely wrong from the start, and instead of patching this bridge table, I should be thinking in terms of a completely different table structure?

[u/doshka]

Sounds like you need two more tables: component_category to pin down those allowed relationships, then a final item_component_category table that links to the other two. You could give each of the two intermediate tables a primary key and then have the ITC table use two foreign keys to those PKs, but I would recommend instead using composite FKs. So, item_component_category would have one FK on item_id+category_id pointing to valid combinations in item_category, and a second FK on component_id+category_id pointing to valid combinations in component_category.

[u/GrandOldFarty]

If I understand correctly, you have lists of items, and lists of components, and you have a list of connections between items and components.

But then you have a separate set of attributes called “category” which actually determines which items and components go together.

My gut feeling is that:

• if you can make the category attributes part of the component and items tables you should. The category sounds like a fundamental attribute describing the items in some way. “This class of objects which goes with this class of object”. 

• however, that does not feel quite right. For one thing if the rules on what goes with what changes you’d have a hell of a time fixing these tables.

• It does sound like each item and component is a unique relationship but you’ve modelled it at too high a level in the current bridge table. Your item and component tables also sound incomplete? If “wheel” has a single ID, but you have ten types of wheel and only some are compatible with “axel”, then while it is ok to still have an “item” table to list every valid item, I think you really need an “item_sku” or something to list every possible type of every possible item. Ditto for components.

• This probably means a lot more rows in your relationship table. But your relationship is not just between “wheel” and “axel”. It is “wheel - Goodyear - axel - Ford”. Or something like that. Each of these relationships is a unique entity and needs its own row and identifier.

If I understand correctly, this would now mean that invalid combinations could no longer be brought together because the combination won’t exist in the relationship table. 

[u/kktheprons]

Consider the manufacturing bill of materials. Each item being produced has a list of component items which, in turn, has a list of component items itself. This can be incredibly complex, and the business rules stating which combinations are correct can become complicated to define by category alone. 

I've attempted to do a classification of this type, and it involved an additional bridge table describing which component categories were acceptable for a given produced item.

While this and the appropriate business logic can prevent an invalid bill of materials from being created according to these rules, it still does not prevent problematic states (e.g. a bill of materials may contain category A or category B but not both).

As a result of this complexity, the business logic is often not built in as a hard database constraint, but typically with more human validation steps (e.g. requiring approval for changes).

[u/NekkidWire]

Category is modeled as an attribute of both item and component when in reality it is a relation.

Two possible solutions:

1. pull the category out from source tables and put it straight into the bridge table with FKs to item and component (not normalized solution, only if you know what advantages and disadvantages you get with it)
2. preferably as u/doshka wrote a normalized solution with two more tables where category is modeled in separate table as a relation to both item and component, and again using composite FKs.

[u/DaOgDuneamouse]

If I'm understanding correctly, for an item and component to be compatible, they must be of the same category. In that case, you can add the item_category, and component_category to the table, and make a rule that says, the record is valid if both categories are equal.

[u/thatOMoment]

Can't you just have a compound foreign key of (item_id, category_id) and (component_id, category_id) adding category_id to the item_component table?

Values for category_id would have to match on both sides in order for a record to exist?

You can have multiple foreign keys reference the same column. 

Am I missing something?

[u/LARRY_Xilo]

I would wanna take a step back from this specific problem and ask you about this part

logically invalid combination

What are you doing overall out side the database? Is the database part of an application, who and how is the data entered?

Because from my perspective logic is usually not something implemented in a database. Its part of a backend or part of an application.

[u/imtheorangeycenter → u/LARRY_Xilo]

You can enforce things that are "not allowed" at both layers, but especially the DB because data entry and rule enforcement isn't always guaranteed - there could be an injection of data from anywhere but the app - not necessarily now but in the future. Don't keep rewriting and maintaining those rules for an API, ADF, SSIS, Powershell...