Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2009
    Location
    Portland, OR
    Posts
    8

    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?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2009
    Location
    Portland, OR
    Posts
    8
    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

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jan 2009
    Location
    Portland, OR
    Posts
    8
    thanks again. ;-)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •