I am curious, how would you guys design a database schema that would enable you to store and retrieve criminal data.

My friend designed his database so that it has a Incident, Person, and Case table. In order to map the relationship together, he uses a 4th and 5th table called "link_Incident_Person" and "link_Incident_Case" . Each of the link tables has 2 columns where the relationship of the two tables is stored.

This seems like an incredibly inefficient way of doing this.

Isn't there a better way to do this using foreign key relationships or something? If I am correct, can someone explain to me how to explain it to him?

those are foreign key relationships in those two linking tables

they are the most efficient and "best practice" way of implementing a many-to-many relationship

if each incident can involve one or more persons, and each person can be involved in one or more incidents, and if each incident can involve one or more cases, and each case can be involved in one or more incidents, then that's exactly what you need -- many-to-many relationships

i don't think your friend needs to have anything explained to him

Thanks for your advice. I think I understand it now. I found a webpage that mentions it being called a "junction table" or "union table".

Database Design - Many-to-many

And then this page explains why this method is good for making the database flexible for future relationship changes (which is the main reason why i didnt understand: because i was assuming a unchanging design where FKs would handle it ) :

http://www.tekstenuitleg.net/en/arti...ign_tutorial/8

that's an excellent explanation, and some first rate diagrams

it is more often called a relationship or association or linking or many-to-many table

junction is used infrequently (it hearkens back to pre-relational CODASYL days), and union is hardly ever used because UNION means something else

thanks again. ;-)