How to design a database that stores criminal data
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
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 ) :