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!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|||You can use a ClientCase table with a many to many relationship between your Clients and your Cases tables.


Clients ClientCase Case
------ ------ ------
CliNumber --> CliNumber
CaseNumber <-- CaseNumber
Cliname CaseLeadAtty
CliAddress CaseSecondAtty


etc.|||tomh53,

Just curious...
What would be the need for the intermediate table, unless
one case number can have multiple clients?|||... 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!!

:) :)|||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?|||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. :)