Storing an email communications history in database

[dominicall]

Hi

I'm developing a site for a client that provides the functionality to make selections from the database and then send emails to his clients from the site - that part is the easy bit and is already complete.

Am struggling to get my mind around the architecture of storing the communications history, i.e how do I most efficiently store the full communications history for each client.

Assumptions are the I've got a clients table which is fine and a table for storing the campaign summary for each campaign (date, campaign code, copy, SQL used for selection)

My initial thought was to have a promotions history table with roughly the following structure...

Column1: ClientID - int (linked to Client table)
Column2: CommsHistory - varchar

Then for each campaign the campaign-code from the main campaigns summary table is appended to the existing value in the CommsHistory column... e.g.

Client 200 has received 3 campaigns with campaign codes 1234, 5678, 9876

Client 201 has only received 2 campaigns with codes 1234, 7621

Therefore, the data stored in the history table will be (have used the code tags to use tabs)

ClientID CommsHistory
200 1234,5678,9876
201 1234,7621

This would work because I could then select clients by which campaigns they had received (or not) in the past, but I'm sure there must be a better way to do it than this.

Have designed many databases before but not a comms history function and am not sure this is the best approach.

Any suggestions/thoughts gratefully accepted.

Thanks

Dominic :D

[rcreyes]

The best way to do this is to create a one to many table relationship, for example:

Client Table:
-------------------
Client ID
Client Name
Client Address


History Table
------------------
HistoryId
Client ID
Communication Code

Each client record can have zero, one or many records in the history table (communication Code)


Using your example, Client ID 200, will have 3 records in the history table, this record will look like this:

HistoryID = 1
Client ID = 200
Communication Code = 1234

History ID = 2
CLient ID = 200
Communication Code = 5678

History ID = 3
Client ID = 200
COmmunication Code = 9876

To select all records from History that belong to client ID 200, you will simplt issue a SELECT * FROM History Where ClientID=200

To add another communication code you will use the INSERT statement, for example


Insert INTO History (HistoryID, Client ID, Communication Code)
Select 4, 200, 4545

You can use an IDENTITY column for HistoryID

Hope this help......

Thanks,
Ray

[dominicall]

Thanks Ray

That was my original plan but I started doing some calculations on the extra space that would take up in the database so was looking to see if there was a more efficient way of doing it.

Will test both ways I think.

:D

Dominic

[rcreyes]

You are welcome, just a comment on space

You will use less space if you create a History Table like the ff:

HistoryKey INT
ClientID INT
Communication Code INT


instead of your original concept which is:

ClientID INT
Communication Code VARCHAR(255)

you will waste more space using the varchar data type because not all record will use all the space in this columns. You might have a record with just one communication code, and you will just waste the extra space in this column.

I used varchar(255) here, but you can assign any number here, but either way, some client records could hit the limit of this column, i.e. if you defined it as varchar(50), then the max communication code you can attach to this client is about 8 codes....

Thanks,
Ray

[dominicall]

Cool - thanks Ray

:D :D

Dominic