How to implement Many to Many relationships?
I've been google searching for articles on implementing many-to-many relationships and finding so many hits I'm overwhelmed.
Does anyone have a favorite article on this subject?
In my example, I have a many to many relationship between table "user" and table "project".
Why do some articles suggest that the primary key for the link table "UserProject" contain the two foreign keys for "user" and "project"? If we want to find all the projects for a given "user.id" we won't be specifying the "project.id". Won't this degrade into a linear search then?
I was thinking we should pick the foreign key "fk_user" as the primary key and create an index of the for "fk_project" (or vice versa). Then if we specify the "user.id" we can quickly find all the corresponding "project.id" values.
Are there any issues unique to oracle that I should know about?
Thanks
Siegfried