In a many-to-many relationship, a record in one table can have many linked records in a second table, AND a record in table 2 can have many linked records in table 1. A common way to physically model this relationship in a relational database is using an intermediate table, often called a junction or join table.

For example, a music database needs to keep track of which musicians appear on which music albums, and further what instruments they play. An individual musician can perform on many different albums, as the artist of the album or on other artists' albums. Furthermore, an individual music album can obviously have many different musicians recorded. This is a many-to-many relation between musician and album.

Another relation to track is between the musician and the instrument. A given musician may play guitar, keyboard and saxophone; and obviously, the saxophone is played by many musicians. So to maintain detailed record of who plays what, a many-to-many relation could be in order. (You might only desire tracking from musician to what instruments she plays; but many-to-many also allows you to start with the instrument, and more easily query about who plays it).

Here is what the structure might look like in 4D to model these two many-to-many relations:



There are two join tables, the MusicianInstrument and MusicianAlbum tables. The fields are indexed, and are each related many-to-one back to the principal tables. In essence, the join table has two foreign keys, which together make the primary key of the join table. So for instance, the MusicianInstrument join table's two indexes are related many-to-one back to the primary key ID fields of the Musician and Instrument tables.

Note that other data fields can be added to the join tables. In the MusicianAlbum table, a "Date" field might be added to record when a particular musician recorded on a particular album. In the MusicianInstrument table, a "Skill" field could record how proficient a musician is on a particular instrument. "Skill' is not an attribute of the musician himself, but of the many-to-many relation: say the skill of Stevie Wonder specifically on the harmonica.