meny to meny relationships
- Thread starter inbal
- Start date
hi, you can not have many to many relationships....
you have to decompose it....
you have to create a "Juntion Table", which is an intermediate table that serves as a bridge between the two tables. it uses the primary key from the 2 tables as its own primary key.
hope that helps.
Rabbie
Super Moderator
- Local time
- Today, 02:18
- Joined
- Jul 10, 2007
- Messages
- 5,906
You model a many to many relationship by having a 1 to Many relationship between your two tables and a junction table. For example a book can have many authors and author can write many books
The junction table would hold records like this
BookID AuthorID - These fields would hold the PK from the Book and Author tables as foreign keys. If you define the PK of the Junction table as a composite key made up of both fields you can easily ensure you don't store duplicate records in the junction table.
This structure makes it easy to list all books by a certain author and also all the authors of a particular book while still keeping your data normalized.
The junction table would hold records like this
BookID AuthorID - These fields would hold the PK from the Book and Author tables as foreign keys. If you define the PK of the Junction table as a composite key made up of both fields you can easily ensure you don't store duplicate records in the junction table.
This structure makes it easy to list all books by a certain author and also all the authors of a particular book while still keeping your data normalized.
