Results 1 to 7 of 7

Opinions on Table design

  1. #1
    dniezby is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Apr 2013
    Posts
    162

    Opinions on Table design

    I'm creating a prospecting database that will be tracking prospects through our system. There are basically 3 activities that I need to track.
    Individual Emails - track all the emails that are sent to a prospect
    Texts - Track all the texts that are sent.
    Group emails - These will be pre-written emails that are sent to the entire table of prospects.


    I'm creating 3 separate table to track each one of them and they will be linked to the prospect ID. Do you think this is a good way to do this or do you have better method of doing this?

  2. #2
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    7,570
    You could combine individual emails and texts into one table with an extra field to indicate the method used. Group emails could be in a separate table or could be included in the other table with a null family key although this does mean you would not be able to implement referential integrity. All depends on your processes and business rules.

  3. #3
    dniezby is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Apr 2013
    Posts
    162
    Quote Originally Posted by Ajax View Post
    You could combine individual emails and texts into one table with an extra field to indicate the method used. Group emails could be in a separate table or could be included in the other table with a null family key although this does mean you would not be able to implement referential integrity. All depends on your processes and business rules.
    Good idea combining the text and email tables. I guess what I could do is create a single form with hidden fields until the message type is selected. So, if it's a "text" message then I make visible the text message field and vice-versa. Then code the send button to do all the email work using outlook.

  4. #4
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,456
    What does 'track emails' and 'track texts' mean? Keep the actual message in the db? Access the actual message from the db as well? Or just record the fact that one or the other or both were sent? If it's purely the activity you want to record, then your original idea is more normalized. The extreme opposite would be to have all in one table with a prospect ID and an activity field for each type, which would potentially result in a lot of "holes". Combining more than one activity type in any one table is not the best as far as achieving normalization is concerned. If you had to add an activity type in the future, then what? Modify a table to add the appropriate field? We try to avoid that as much as possible, and as soon as you combine one or more activity types in one table, you run that risk. You have to decide if the meager effort in having an activity table for each type is not worth it to you.
    - "doesn't work" is no help. Post err msgs and where.
    - Use code tags for code/sql. Implement changes in copies of your database.

  5. #5
    dniezby is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Apr 2013
    Posts
    162
    Quote Originally Posted by Micron View Post
    What does 'track emails' and 'track texts' mean? Keep the actual message in the db? Access the actual message from the db as well? Or just record the fact that one or the other or both were sent? If it's purely the activity you want to record, then your original idea is more normalized. The extreme opposite would be to have all in one table with a prospect ID and an activity field for each type, which would potentially result in a lot of "holes". Combining more than one activity type in any one table is not the best as far as achieving normalization is concerned. If you had to add an activity type in the future, then what? Modify a table to add the appropriate field? We try to avoid that as much as possible, and as soon as you combine one or more activity types in one table, you run that risk. You have to decide if the meager effort in having an activity table for each type is not worth it to you.
    Yes, I'd like to keep the actual message in the database. At least when it comes to text messages.

    Ideally, what I would like to do is write some pre-written emails. Save THOSE in a table Then when I want to email someone select it from the list and BAM - off it goes to the prospect. THEN all I would need to reference in the activity is the ID and Subject line (as the plain English name).

    I don't know how to do that though.

  6. #6
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    7,570
    So, if it's a "text" message then I make visible the text message field and vice-versa.
    you would just have the one 'message' field, but if it is a text message some validation code to limit to 160 chars whatever.

    Ideally, what I would like to do is write some pre-written emails. Save THOSE in a table Then when I want to email someone select it from the list and BAM - off it goes to the prospect. THEN all I would need to reference in the activity is the ID and Subject line (as the plain English name).
    this is what I mean about business rules and processes. Access is very flexible and can handle pretty much anything, but unless you are clear about what you want, it is not possible to design it properly in the first place. It's like saying 'I want to build something that takes me from A to B'. That could be anything from a pogo stick to a spacecraft.

  7. #7
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,456
    Yes, store the email message in a table field. If you need formatting it should be possible to have mixed font characteristics if the table field is memo type, has its Format Text property set to Rich Text and Rich Text property is chosen for the control on the property sheet data tab as well. If you can identify the email you need with a descriptor of some sort, a combo box or list box would be sufficient for choosing the email from a list, or you could display the email text values in a continuous form if there aren't that many.
    Then you'd send the email based on the selected record. How you create and send the email depends on your circumstances and process. Google 'ms Access send email' or something like that and research the various methods. SendObject, CDO and Automation would be 3 ways I can think of, Automation being the most difficult and I think not needed in your case.

    As for sending text messages (SMS?) I cannot offer any advice specific to using a GSM modem with Access.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 09-21-2013, 11:36 PM
  2. Table Design -- want to avoid a design that creates blank fields
    By snowboarder234 in forum Database Design
    Replies: 4
    Last Post: 10-08-2012, 10:13 AM
  3. Replies: 4
    Last Post: 01-13-2011, 08:34 AM
  4. Opinions on PL/SQL and pass through query
    By thart21 in forum Queries
    Replies: 5
    Last Post: 06-02-2010, 11:08 PM
  5. My Access Upsizing Nightmare...Opinions and Thoughts?
    By Jerimiah33 in forum Import/Export Data
    Replies: 0
    Last Post: 01-30-2007, 12:40 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums