Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2005
    Posts
    165

    Unanswered: Best way to associate one to many setup...

    Hi. We're trying to setup a new case management system and I'm having a problem trying to store some of the information. I think I know how to go about it; however, I'm not sure if it's right.

    We have two tables: Cases and Clients. The cases table stores relevant case information like case number, case type, etc. The Clients table stores information like name, address, phone number, email, etc.

    What is the best way to associate these two tables together? I'm assuming I need a third table. Right? Thanks!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you will forgive me if i don't intuitively know what a "case management system" is supposed to do

    presumably a client can have more than one case?

    but can a case involve more than one client? if so, then yes, you need a third table, otherwise just link each case to its client
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2005
    Posts
    165
    Sorry, I may have left out some information.

    We will only have one case. However, each case can have multiple clients. We have a CaseID and a ClientID in each table serving as the primary key. What would I need in the third table? I was thinking:
    Code:
    CaseID
    ClientID
    Would I need a primary key for this table? Or are the two columns sufficient?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    two columns are sufficient

    the PK should be a composite key consisting of both columns
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jan 2005
    Posts
    165
    Okay. Thank you so much. I thought it was right--I just wanted to make sure.

  6. #6
    Join Date
    Aug 2006
    Location
    San Francisco, CA
    Posts
    136
    Can one client only be apart of one case or can the client be apart of many cases?

    If the client can only be apart of one case then you can bring the CaseId into the client table.

    Also if your case number is unique you don't need a caseid

  7. #7
    Join Date
    Jan 2005
    Posts
    165
    Each case can have multiple clients. And clients can have multiple cases. So this is why I was asking about needing the third table to relate cases to clients.

  8. #8
    Join Date
    Aug 2006
    Location
    San Francisco, CA
    Posts
    136
    yes, if it is many to many relationship you need a third table

Posting Permissions

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