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

    Unanswered: Creating a normalized database

    Hi. I have a project I need to complete and I really don't know the first step I should take. Basically, we have a case management system that is normalized for the most part except for one major flaw: the clients table. Whenever we add a new case, we have to add the customer's name/address/phone etc. all over again. I would like to redo this current setup so we have one table just for clients and another table just for cases, so we don't have all this double entering all the time. Is there an easy way to do this or could someone point me in the right direction? It's on a SQL Server 2000 database. Thanks for your help!

  2. #2
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    4 easy steps

    CREATE the client table (ClientID, ClientName, Address...)

    SELECT DISTINCT Client info from the case table into the new Client Table

    Build Relationship between the 2 tables (on ClientID)

    DELETE the redundant columns from case table
    Inspiration Through Fermentation

  3. #3
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    You can use a ClientCase table with a many to many relationship between your Clients and your Cases tables.
    Code:
     
     
    Clients              ClientCase             Case
    ---------------- ------------------ -----------------
    CliNumber  ----> CliNumber
                     CaseNumber <---     CaseNumber
    Cliname                              CaseLeadAtty
    CliAddress                           CaseSecondAtty
    etc.

    -- This is all just a Figment of my Imagination --

  4. #4
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    tomh53,

    Just curious...
    What would be the need for the intermediate table, unless
    one case number can have multiple clients?
    Inspiration Through Fermentation

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by RedNeckGeek
    ... unless one case number can have multiple clients?
    if a case only and forever belongs to only one client, then yeah, you don't need the many-to-many relationship table

    however, note that you can implement a one-to-many relationship using a many-to-many relationship table -- just make sure (in your app logic) that you never store more than one client per case!!

    then, when the day comes, and the case rolls in which requires two clients, you're all set!!

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    Quote Originally Posted by RedNeckGeek
    tomh53,

    Just curious...
    What would be the need for the intermediate table, unless
    one case number can have multiple clients?
    How about a class-action lawsuit?

    -- This is all just a Figment of my Imagination --

  7. #7
    Join Date
    Jan 2005
    Posts
    165
    Quote Originally Posted by RedNeckGeek
    4 easy steps

    CREATE the client table (ClientID, ClientName, Address...)

    SELECT DISTINCT Client info from the case table into the new Client Table

    Build Relationship between the 2 tables (on ClientID)

    DELETE the redundant columns from case table
    Thank you! That worked like a charm. I still have a few duplicates but it beats going through all of them manually.

Posting Permissions

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