Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    Regular Coder
    Join Date
    Sep 2002
    Location
    London
    Posts
    179
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Storing an email communications history in database

    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)
    Code:
    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
    dominicall - confusing himself more and more each day

  • #2
    New Coder
    Join Date
    Oct 2002
    Location
    nj
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

  • #3
    Regular Coder
    Join Date
    Sep 2002
    Location
    London
    Posts
    179
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.



    Dominic
    dominicall - confusing himself more and more each day

  • #4
    New Coder
    Join Date
    Oct 2002
    Location
    nj
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

  • #5
    Regular Coder
    Join Date
    Sep 2002
    Location
    London
    Posts
    179
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Cool - thanks Ray



    Dominic
    dominicall - confusing himself more and more each day


  •  

    Posting Permissions

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