Page 1 of 3 123 LastLast
Results 1 to 15 of 31
  1. #1
    Star Lounger
    Join Date
    Jan 2001
    Posts
    65
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Table Design Help For Study Contacts Database (Access 2003)

    I am trying to design tables for a database using Access 2003 that will
    allow me to retrieve information about Bible study contacts. I had a pretty
    good concept on how to do this dealing with individual contacts but now I
    realize that some of my studies with people will involve several contacts at
    once, some having the same address but different phone numbers and email
    addresses. I am trying to sort out what type of tables I need to get the
    job done where when I look for active studies that I can see all related
    people involved.

    Do I need to have one table with just ContactID and ContactFirstName and
    Lastname and then split off the Contactaddress table because more than one
    contact can have the same address and I do not want to retype that
    information of course.

    As always, I am probably not explaining it as clearly as possible but to
    summarize my database is composed of following contact information (name,
    address and so on), visitor information (how often they have visited with us
    to worship) and study history information (time of study, topics studied and
    their responses to those studies). Your help is always greatly appreciated.

    Gary Hunt

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Table Design Help For Study Contacts Database (Access 2003)

    Can some contacts have multiple addresses, for instance a home address and a work address? Or is it just that several contacts can have the same address, but each contact has only one address?

  3. #3
    Star Lounger
    Join Date
    Jan 2001
    Posts
    65
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Table Design Help For Study Contacts Database (Access 2003)

    Hans,

    Thanks but I have not decided that part yet. Right now, I was just going to track their home address and not work.l I would track their work phone, email and so forth. Therefore, right now, several contacts could have the same address but only one address per contact. Also, several contacts could particiapate in the same study sessions or in several different study sessions.

    Any help would be appreciated. Will clarify anything else needed if I can. I am in the early developement stage of this but would really like to get this done. I know it would be a great help to me. Besides studys and visits to our public services, I also want to track any interactions (calls, letters and so on) that lead to a study session.

    Thanks again.

    Gary Hunt

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Table Design Help For Study Contacts Database (Access 2003)

    You should decide now whether you want to provide for multiple addresses per contact. If you store only one address now and discover later on that you need more, it will be difficult to modify the structure of the database. If it is only one extra address, you can keep it simple, though.

    With the simple structure, you were on the right track:
    - A table tblAddresses with an AutoNumber primary key AddressID, plus the information about the address.
    - A table tblContacts with an AutoNumber primary key ContactID, plus all information about the contact; the address is stored in the form of a number (long integer) field AddressID.
    - tblAddresses and tblContacts are linked on AddressID (one-to-many); set referential integrity for the relationship.

    - A table tblStudies with an AutoNumber primary key StudyID, plus information about the study.
    - An intermediate table tblStudyContacts, with number (long integer) fields StudyID and ContactID, plus perhaps information about the role the contact has in the study. The combination of StudyID and ContactID forms the primary key.
    - tblStudies is linked to tblStudyContacts on StudyID (one-to-many), and tblContacts is linked to tblStudyContacts on ContactID (one-to-many); set referential integrity for both relationships, with cascading updates and cascading deletes.
    - The two relationships implement a many-to-many relationship between tblStudies and tblContacts: one contact can participate in several studies, and several contacts can participate in one study.

    Other tables will be linked to tblStudies too.

  5. #5
    Star Lounger
    Join Date
    Jan 2001
    Posts
    65
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Table Design Help For Study Contacts Database (Access 2003)

    Hans,

    That helps me a lot. I am very thankful. Regarding tracking work address would I need separate tables for work and home address information? I guess it might be better to track both types of address and if I need work for some that will be fine but if not, then I can leave this part blank. Am I on the right track with a workaddress table with a workaddress ID linked to ContactID?

    Thanks again.

    Gary Hunt

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Table Design Help For Study Contacts Database (Access 2003)

    Use only one table tblAddresses to store both work addresses and work addresses. For the "simple" solution, use two address fields in tblContacts: HomeAddressID and WorkAddressID, both linked to AddressID in tblAddresses.

    If you like, you can add a field AddressType to tblAddresses, for example 0 = home, 1 = work. This would enable you to list only home addresses in a combo box, or only work addresses.

    The "complicated" more flexible solution would involve an intermediate table tblContactAddresses with ContactID and AddressID, much like the tblStudyContacts table.

  7. #7
    Star Lounger
    Join Date
    Jan 2001
    Posts
    65
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Table Design Help For Study Contacts Database (Access 2003)

    Hans,

    Thanks again. I guess I would go with the more flexable solution although it will increase my learning curve somewhat. If I can after some days of thinking about these things, I would like to post my progress regarding my table structure. I know that if I don't get this the way I want it, it will be big problems later.

    Thanks again.

    Gary Hunt

  8. #8
    Star Lounger
    Join Date
    Jan 2001
    Posts
    65
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Table Design Help For Study Contacts Database (Access 2003)

    Hans,

    I guess I will take a shot at the more complicated solution because it will be more flexable. After a while, I will post back my tables structure progress if that's OK because I know if I don't get this right, it will cause big problems later.

    Thanks again for your fine advice.

    Gary Hunt

  9. #9
    Star Lounger
    Join Date
    Jan 2001
    Posts
    65
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Table Design Help For Study Contacts Database (Access 2003)

    To Hans or anyone who might be able to help me. I have decided to track only home addresses of my Bible study contacts. However, I am wondering now if I should only record the street address and city and state in one table linked to my Contact table with streetaddressID. I would leave out phone infomration and email and some other things that might differ among contacts. This way I would not have to retype a street address if two contacts have the same street address but they might have different phone numbers. If I place phone numbers with street address, then I would have to create a new address entry. Am I making snese with this or am I missing something in trying to design these tables? Hope I am not confusing things in the way I am running this thread. Thanks for your help.

    Gary Hunt

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Table Design Help For Study Contacts Database (Access 2003)

    I think you're on the right track. If contacts with the same address have different phone numbers etc., keep the phone numbers in the contacts table, not in the address table. The purpose of having a separate address table was to avoid having to duplicate the same address. Creating records with the same address but differing phone numbers defeats that purpose.

    Is a piece of information uniquely determined by the address? If so, it belongs in the address table. Otherwise, in the contacts table.

  11. #11
    Star Lounger
    Join Date
    Jan 2001
    Posts
    65
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Table Design Help For Study Contacts Database (Access 2003)

    Hans,

    As always, thanks and at least now I feel better about beginning this design.

    Gary Hunt

  12. #12
    Star Lounger
    Join Date
    Jan 2001
    Posts
    65
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Table Design Help For Study Contacts Database (Access 2003)

    To Hans or anyone that can help me.

    I just realized I need to make a possible design change in my study contacts data base. I will try to explain as much as possible what I want and how I think I can accomplish it and whatever is lacking, please let me know.
    I will be tracking Bible studies that I will be conducting with both members of my local church as well as those who are not members. The type of material that I will teach depends on which type of person I will study with. Therefore, in my Contacts table I will need to be able to distinguish between members and nonmembers. For this I might create a ContactTypes table that will alow me to change this easily if someone decides to become of a member of the local church. In other words, anyone who will be teaching, supporting or being a student in the studies will be in the Contacts table, including me. Maybe a better name for that table is needed.

    Also, I will need to have a table that tracks studies with studydate, studytopic, studylocation and people who attend the study and what roles they play, teacher student or supporting. Therefore, I think I will need a table To cover the Contacts addresses, I will have a separate addres table but I will need to have studylocation addresses there also if the studylocation is different than one of the participants (eaith teacher or student) in the study. Can I just use the AddressID field in either the studys table and the contacts table?

    Also, for role of contact, can I create a small lookup table listing the roles and just choose from there to fill in that part in my studys table.

    Also, I will need fields for proposed study date, location and topic to let me know what studies I have scheduled. I do not know where to put these fields.
    I hope this is not confusing but your input is highly valued. From my experience this forum is one of the most responsive that I have seen and it is greatly appreciated. Thanks.

    Gary Hunt

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Table Design Help For Study Contacts Database (Access 2003)

    Let's make things even more complicated <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>.

    Does a study always take place on a single date in a single location, or can a study be spread out over several days, and possibly take place in a different location each day? If so, that would mean - I think you can see it coming - another intermediate table. If not, you can store the date and location ID in the Studies table.

    Another thing to consider: you can make each study a separate record, so if you do a study about the prophet Jeremiah in 2003 and another one (with different participants) in 2004, you have two records. You could also create a table with unique study subjects ("Jeremiah"), and another table with "instances" of these subjects ("Jeremiah 2003" and "Jeremiah 2004"). Depending on your choice, the date and location of the study go into the Studies table or into the StudyInstances table.

    For the participants in a study, you do need an intermediate table: ContactID, StudyID (or StudyInstanceID) and RoleID. You shouldn't put this RoleID in the Contact table, for perhaps somebody will be a student in one study, but playing a supporting role in another one.

    You can create lookup tables for ContactTypes (member, non-member), for RoleTypes (teacher, student, support).

  14. #14
    Star Lounger
    Join Date
    Jan 2001
    Posts
    65
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Table Design Help For Study Contacts Database (Access 2003)

    Hans,
    I hope one day, I will have a fraction of the knowledge you have about this program and how to use it. Anyway, a study is composed of a topic, either prepared ahead of time or one that is more spontaneous. If we do not finish a topic on a certain date, it may be carried over to another date and possibly another location. Then, there might be studies that cover the end of one topic and the beginning of another. Mainly, I am just trying to keep up with what topics I will be and do study with different people. From this,.maybe I need to have a studytopics table along with studyinstance table but I am not quite sure I fathom this yet. Could you go over a senario agan on this?

    Also, although an address may be only a study location and not a contacts address, I still could put them all in the Addressses table without any problem?

    And, what about the scheduled place date and topic information if that would be needed?

    Thank you and I know I have made it more complicated but I do see the need to try and get it right in the beginning rather than building yourself a box that you try to break out of later. Thanks as always.

    Gary Hunt

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Table Design Help For Study Contacts Database (Access 2003)

    Gary,

    If the subject can be spontaneous, and if some studies cover several topics, it might be better to use a Studies table as central object. If needs be, you can always add keywords later on. A record in this table is a specific instance of a study.

    You can put all addresses in a single Addresses table. It may be useful to have an AddressType field to distinguish between participant addresses and other addresses (study locations only).

    And now we come to a decision point again.
    <UL><LI>If a study can take place on more than one date and location, and if you want to be able to do some scheduling, you'll need another table beside Studies: StudyDates. Fields in Studies would be StudyID and Topic; fields in StudyDates would be StudyID (linked to StudyID in the Studies table), Date, LocationID; for each day a separate record.
    <LI>If it's OK to record only the date and location of the first session of a study, you can omit the StudyDates table, and put the Date and LocationID in the Studies table. This is simpler, of course, but you won't be able to schedule the second session of a study.[/list]

Page 1 of 3 123 LastLast

Posting Permissions

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