Mysql – Types of ways to achieve Many-to-Many relationship between tables in DB

Why must the employees working on a project strictly belong to the same division that owns the project? That sounds more like a business rule that you can leave up to your application to enforce rather than something that requires DRI.

But let's say you need to enforce this using DRI. You said you have a many-to-many table associating employees with projects. Assuming you are using SQL Server, you could expand this table as follows:

CREATE TABLE dbo.Employee_Project (
     ProjectID            INT
   , ProjectDivisionID    INT

   , EmployeeID           INT
   , EmployeeDivisionID   INT

   , Hours                INT

   , CONSTRAINT FK_Project FOREIGN KEY (ProjectID, ProjectDivisionID) 
        REFRENCES dbo.Project(ProjectID, DivisionID)

   , CONSTRAINT FK_Employee FOREIGN KEY (EmployeeID, EmployeeDivisionID) 
        REFERENCES dbo.Employee(EmployeeID, DivisionID)

   , CONSTRAINT CK_EmployeeProjectSameDivision CHECK (ProjectDivisionID = EmployeeDivisionID)
);

This is what's going on here:

• We have two composite foreign keys here, one for Employee and one for Project, so we can have the DivisionID for both entities in the same table and guaranteed by DRI to be correct.
• These composite foreign keys require a UNIQUE index on the referenced tables. So in addition to your primary keys on EmployeeID and ProjectID in those tables, you'll need unique keys on (EmployeeID, DivisionID) and (ProjectID, DivisionID).
• The CHECK constraint guarantees that an employee can only be assigned to a project owned by the same division.

If you want to see another example of this design pattern, here's the answer I gave to a design problem that had similar requirements.

Here is a suggestion so you can enforce the constraints you want declaratively. (I've simplified the table names a bit, removed the bridge_ prefix.)

We remove footnote_num from:

Table:      a_ref         -- was named:  bridge_a_reference
     a_id, 
     ref_id
Primary key: 
     (a_id, ref_id)
Foreign keys:
     a_id  -> a
     ref_id -> reference

We add this table - which will basically store only those rows from a_ref with footnote, those you want to add children into the b_ref:

Table:      a_ref_with_footnote 
    a_id, 
    ref_id,
    footnote_num
Primary key: 
    (a_id, ref_id)
Unique key:
    (a_id, footnote_num)
Foreign keys: 
    (a_id, ref_id)  -> a_ref

And finally the 3rd table stays as in your design except the foreign keys which now reference the intermediate table (a_ref_with_footnote):

 Table:     b_ref         -- was named: bridge_b_reference:
     a_id, 
     b_id, 
     ref_id,
 Primary key: 
     (b_id, ref_id)
 Foreign keys: 
     (a_id, b_id) -> b
     (a_id, ref_id) -> a_ref_with_footnote