In relational database design, a many-to-many relationship is not allowed. Consider the example of keeping track of invoices. If there were “many” invoices with the same invoice number and one of your many customers called up asking about that invoice number; how would you know which specific one they were referring to? The answer is you could not. An invoice should have a unique “ID.”
To get around the problem of having a many-to-many relationship you need to break apart the many-to-many relationship into two one-to-many relationships. Using a third table, commonly called a “join table”, does this. Each record in the “join table” would have the foreign key fields of the two tables it is joining together. Nothing special needs to be done with the foreign key fields in the join table as they will get populated with data from the other two tables as records are created. It is not uncommon for a join table to have a lot of records in it – since records are created in the join table as records are created in the two tables it joins.
The diagram in figure 6 also indicates that there is a many-to-many relationship between Invoices and Products. You cannot set up a many-to-many relationship directly between two files.
For example, if you create a relationship between Invoices and Products based in a field named Invoice ID, each product can appear on only one order. If you create a relationship based on a field named Product ID, each invoice can have only one product
Database normalization is the process of organizing the fields and tables of a relational database to minimize redundancy and dependency. Normalization usually involves dividing large tables into smaller (and less redundant) tables and defining relationships between them. The objective is to isolate data so that additions, deletions, and modifications of a field can be made in just one table and then propagated through the rest of the database via the defined relationships.