Database Design

[cdoggg94]

Hopefuly I can explain this so that it makes sense..

 

I have attached a picture that has been divided into 3 parts: Services, Contact info, and Treatment.

 

Right now I have the information for them all in one table and everything is working fine.

 

My client has requested that they would like to see previous estimates for Services and Treatment.

 

Is there a way around creating a new table for each client then just inserting the record when update is pressed instead of updating the information in the current table that all contacts are in?

[scootstah]

Can we see the actual database layout? Run this query and post the results (replace tablename with the name of the table):

SHOW CREATE TABLE tablename;

.

[TimeBomb]

As scootstah said, we'll need your database table to give you a good, specific answer.

 

I'll try my best to answer generically, given the small amount of information you have given us.

 

From what I see, you have two problems:

1) You are creating a new table for each client.

2) You are constantly inserting new data into the table, when you should be updating it.

 

The first problem is pretty simple.

Add a clientid column to the original table that holds all the data (the one that currently needs to be duplicated for each client).

Create a new table that links the clientid with the client name and whatever other information that clients may have. Just for these instructions, I'll name this table 'clients'.

Now, instead of needing one table per client, you will only need two. The main table that holds the data, and the table that stores the client information.

Thus, the clientid in the 'data' table syncs up with the id in the 'clients' table.

 

The second problem is a bit more difficult to begin answering because we don't know your implementation. From what you have told us, I can only assume it's as simple as changing:

INSERT INTO data(columnA, columnB) VALUES (dataA, dataB)

to:

UPDATE data SET columnA = dataA, columnB = dataB WHERE clientid = 1

Obviously, using a conditional to check if the row for the client is already created or not in the 'data' table.