Many-to-many relationship database design

I am setting up new MySQL database schema for a small company. We sell products which are collections of raw units of inventory. In the database, I would like to link products with their raw inventory components so I can see the demand (i.e. sales) for each inventory component. This will help with our purchasing strategy. If possible, I would like to avoid a many-to-many relationship in the database design.

I am wondering how to setup this schema in MySQL. The relationship of the columns is as seen below. coll_a is a two piece collection composed of inv_1 and inv_2. coll_b is a three piece collection composed of inv_1, inv_2, and inv_3. How can I restructure this to avoid a many-to-many relationship?

I have thought about creating a single table with an arbitrary id primary key, and then listing each collection with its raw inventory components. I also looked into a bridge table to resolve the many-to-many relationship but I was unable to find a solution. I appreciate any thoughts or ideas!

The only way to avoid a many-to-many relationship is if one or both of the two statements were always true:

• No collection can have more than one inventory component.
• No inventory component can be used in more than one collection at a time.

Clearly, neither statement is true in your business.

Therefore you have described a many-to-many relationship. That is, it is allowed for a collection to have many inventory components, and a given inventory component may belong to many collections.

Here are some tables that implement these entities in MySQL syntax:

CREATE TABLE Collections (
  CollectionId BIGINT AUTO_INCREMENT PRIMARY KEY
);

CREATE TABLE InventoryComponents (
  InventoryComponentId BIGINT AUTO_INCREMENT PRIMARY KEY
);

CREATE TABLE CollectionInventoryComponents (
  CollectionId BIGINT NOT NULL, 
  InventoryComponentId BIGINT NOT NULL,
  PRIMARY KEY (CollectionId, InventoryComponentId),
  FOREIGN KEY (CollectionId) REFERENCES Collections(CollectionId),
  FOREIGN KEY (InventoryComponentId) REFERENCES InventoryComponents(InventoryComponentId),
);

The third table is called the many-to-many table, or bridge table, or intersection table, etc. As far as I know none of these are "official" terms, but are used for the same concept. Use whatever term is most clear for you.

Each row of this table has one pair of id's, referencing one collection and one inventory component. The existence of this pair means that the collection has that inventory component.

Only one row in this table can reference a given pairing, because of the PRIMARY KEY constraint. Either the pairing does belong, or else it doesn't.

You can query all components for a given collection:

SELECT ... FROM CollectionInventoryComponents
WHERE CollectionId = ?;

You can query all collections that a given inventory component belongs to:

SELECT ... FROM CollectionInventoryComponents
WHERE InventoryComponentId = ?;

It's easy to add a component to a collection, simply by inserting one row into this many-to-many table.

You can add more attribute columns to any of the tables. Sometimes the many-to-many table has no need for other attributes, it's simply a record of which components belong to which collections. But you may have other attributes, such as how many of that component are needed, or if the component is optional or mandatory, or the date it was added to the collection, or who added it, etc.