Introducing surrogate key in may-to-many table?
I would suggest that brand_product_id is redundant.
Yes, every table row must be uniquely identifiable.
Nobody ever said that had to be accomplished with a single field.
Logically, this is a &quot;Weak Entity&quot;.  No record can exist in this table without corresponding entries in the other two tables.
It is highly unlikely that you would ever want to query this table without reference to one [or both] of the other two tables, i.e. you would always want to query it for a brand or a product.   Therefore, the [unique] combination of brand_id and product_id is sufficient for a Primary Key.
I would suggest that your biggest decision (if it's a decision at all) is whether to have the Primary Key on (brand_id, product_id) with an index on (product_id, brand_id) for the &quot;reverse&quot; look-ups, or the other way around.  YMMV.