Results 1 to 10 of 10
  1. #1
    myron is offline Novice
    Windows 7 32bit Access 2003
    Join Date
    Jan 2013
    Posts
    8

    Designing a database for community wellfare beneficiaries

    Hello everyone,

    I'm trying to create a database to gather contact details -and if they are legit to apply for- regarding welfare beneficiaries for a community project. So far I came up with this design:

    Click image for larger version. 

Name:	screenshot.1.jpg 
Views:	17 
Size:	77.3 KB 
ID:	10550

    The Goal is:

    - Collect the data - done
    - Assign a handler for each case (one handler multiple cases)
    - Check for contacting the beneficiaries, if their application is valid and comment accordingly
    - Present all Data in a form from which the user can add records

    As a complete newbie I'm not sure how to proceed from here

    Can anyone point to the right direction? And from a design-point-of-view, is my design correct or am I just complicating things here...?

  2. #2
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    Could you explain what type of data is being captured in the fields of the tbl_beneficiaries_dtl?

    Also, you have the join between tbl_beneficiary and tbl_beneficiaries_dtl with the primary keys of both tables (addressID to internalID). Can you explain the relationship between these two tables (i.e. one-to-one, one-to-many)?

    Can a handler ever be a beneficiary? Can a beneficiary ever be a handler?

    Since handlers and beneficiaries are just types of people, should all people be in 1 table and have a field that distinguishes them by type?

  3. #3
    myron is offline Novice
    Windows 7 32bit Access 2003
    Join Date
    Jan 2013
    Posts
    8
    Hi there, thank you for your reply!

    Quote Originally Posted by jzwp11 View Post
    Could you explain what type of data is being captured in the fields of the tbl_beneficiaries_dtl?

    Also, you have the join between tbl_beneficiary and tbl_beneficiaries_dtl with the primary keys of both tables (addressID to internalID). Can you explain the relationship between these two tables (i.e. one-to-one, one-to-many)?
    "contacted", "valid" and "abeyance" are "Yes/No" fields and "comments" is a "memo" field. I'm guessing the relationship between tbl_beneficiaries and tbl_beneficiaries_dtl is "one-to-one" since one record from the first table associates to one and only record from the second table.


    Quote Originally Posted by jzwp11 View Post
    Can a handler ever be a beneficiary? Can a beneficiary ever be a handler?
    For the time being to both question the answer is "No"

    Quote Originally Posted by jzwp11 View Post
    Since handlers and beneficiaries are just types of people, should all people be in 1 table and have a field that distinguishes them by type?
    Perhaps, but there is no interest for the contact details of the handlers as is for the beneficiaries, just their names for the moment. "addressID" field is also considered like a "caseID" (perhaps "addressID" isn't my brightest moment in field naming...). If both handlers and beneficiaries are on the same table even distinguished by a field, how can I assign a handler to one or more cases? Wouldn't that mean I have to split tbl_beneficiaries some how?

  4. #4
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    "contacted", "valid" and "abeyance" are "Yes/No" fields and "comments" is a "memo" field. I'm guessing the relationship between tbl_beneficiaries and tbl_beneficiaries_dtl is "one-to-one" since one record from the first table associates to one and only record from the second table.
    Can the values for contacted, valid and abeyance change over time & is it important to keep a history of those changes? Are you planning on having other similar yes/no fields in the future? Will only 1 memo entry be sufficient over time or do you want to keep a history of the memo entries over time? If the answers to all 3 questions are no, then I would not separate the fields into their own table but rather include them in the beneficiary table. If the answer is yes then we have more restructuring to do.

    Can a beneficiary have many cases over time? If so, how do you plan on keeping the cases separate?

    Perhaps, but there is no interest for the contact details of the handlers as is for the beneficiaries, just their names for the moment
    If handlers & benficiaries are in the same table, the contact detail fields can be left blank for handlers.

    "addressID" field is also considered like a "caseID"
    It is generally recommended that the primary key should have no significance to the user; in fact, the user should never see the primary key which indicates that you should have a separate field to hold the case identification number. You can still use that field as part of an index.

    how can I assign a handler to one or more cases? Wouldn't that mean I have to split tbl_beneficiaries some how?
    If all people were in one table

    tblPeople
    -pkPeopleID primary key, autonumber
    -txtFName
    -txtLName
    other fields

    Now if a person was related to another person as in the case of the beneficiary & handler or a beneficary & their guardian

    tblBeneficiariesAndHandlier
    -pkBHID primary key, autonumber
    -fkPPeopleID foreign key to tblPeople (the primary person: in your case the beneficiary)
    -fkSPeopleID foreign key to tblPeople (the secondary person: the Handler, you could also have another record and use this field to indicate the guardian to the beneficiary)

  5. #5
    myron is offline Novice
    Windows 7 32bit Access 2003
    Join Date
    Jan 2013
    Posts
    8
    The only fields changing in tbl_beneficiary_dtl are "abeyance" -according if papers are missing or not- and "memo" - the handler might change the notes written-. There's no thoughts of similar fields in the near future. There's no need of keeping a history of changes in "memo" field or the other "Yes/No" fields. I guess a "memo" field is enough to keep sort notes about each case. In reviewing the design I see I'm missing two fields: a date for "contacted" and an appointment date...

    Definitely, a handler can have many cases over time or even simultaneously. That's why I thought of a caseID field.

    If all people were in one table

    tblPeople
    -pkPeopleID primary key, autonumber
    -txtFName
    -txtLName
    other fields

    Now if a person was related to another person as in the case of the beneficiary & handler or a beneficary & their guardian

    tblBeneficiariesAndHandlier
    -pkBHID primary key, autonumber
    -fkPPeopleID foreign key to tblPeople (the primary person: in your case the beneficiary)
    -fkSPeopleID foreign key to tblPeople (the secondary person: the Handler, you could also have another record and use this field to indicate the guardian to the beneficiary)
    I have to admit, you lost me there...Do you mean, I'll create two tables, tblPeople and tblBeneficiariesAndHandlier i.e, with similar content (names, addresses etc.) except the PKs and FKs you mention above, in order to be able to connect handlers and beneficiaries?

    I redesigned the base:


    Click image for larger version. 

Name:	screenshot.3.jpg 
Views:	7 
Size:	71.3 KB 
ID:	10564

    I hope now it make more sense...

  6. #6
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    I have to admit, you lost me there...Do you mean, I'll create two tables, tblPeople and tblBeneficiariesAndHandlier i.e, with similar content (names, addresses etc.) except the PKs and FKs you mention above, in order to be able to connect handlers and beneficiaries?
    No, the contact information only goes in one table (tblPeople). You use tblBeneficiariesAndHandlers to relate the people who need to be related and you only make that relationship via the key fields (fkPPeopleID and fkSPeopleID).

    tblPeople would look something like this with the additional fields
    tblPeople
    -pkPeopleID primary key, autonumber
    -txtFName
    -txtLName
    -txtAddress
    -txtCity
    -txtState
    etc.

    Let's say that you have 4 records in tblPeople as follows (I did not include all fields as shown above):

    pkPeopleID|txtFName|txtLName
    33|Harry|Potter
    34|Hermione|Granger
    35|Albus|Dumbledore
    36|Sirius|Black

    Let's say that Albus Dumbledore is the handler for Harry Potter (beneficiary), then the record in tblBeneficiariesAndHandlers would look like this


    tblBeneficiariesAndHandlers
    pkBHID|fkPPeopleID|fkSPeopleID
    1|33|35


    Now let's say that Sirius Black is the guardian of Harry Potter, so now two records in tblBeneficiariesAndHandlers

    pkBHID|fkPPeopleID|fkSPeopleID
    1|33|35
    2|33|36

    Now let's say that Albus Dumbledore is also the handler for Hermione, so 3 records

    pkBHID|fkPPeopleID|fkSPeopleID
    1|33|35
    2|33|36
    3|34|35

    Now a case involves both a handler and a beneficiary which we have already associated with the above table, so we just need to link the case and the 2 people

    tblCases
    -pkCaseID primary key, autonumber
    -CaseNo
    -fkBHID foreign key to tblBeneficiariesAndHandlers
    -valid (yes/no field)
    -abeyance (yes/no field)
    -notes (memo field)
    other fields pertinent to the case

    Since a guardian is independent of a case we would not link the beneficiary/guardian record to tblCases.

    I see I'm missing two fields: a date for "contacted" and an appointment date...
    Instead of having a yes/no field for contacted, just use a date field. If no date is specified, you know the contact has not been conducted. Can a person be contacted many times? Do you want to keep a record of each of those times a contact was made. Can there be more than 1 appointment date?

  7. #7
    myron is offline Novice
    Windows 7 32bit Access 2003
    Join Date
    Jan 2013
    Posts
    8
    I appreciate your time writing down this example!
    I even started to like Harry Potter though I've never liked the films nor the books

    Instead of having a yes/no field for contacted, just use a date field. If no date is specified, you know the contact has not been conducted. Can a person be contacted many times? Do you want to keep a record of each of those times a contact was made. Can there be more than 1 appointment date?
    The date field approach is definitely better, thank you... I've never thought of keeping track of the contacts made or the appointments, in the long run it might become handy. Any suggestions towards that?

    So according to your suggestions the relations of the database will look something like this:
    Click image for larger version. 

Name:	screenshot.4.jpg 
Views:	9 
Size:	120.3 KB 
ID:	10573

    Any thoughts about using "municipality", "province" and "title" as FKs to tbl_beneficiaries_contact_dtl and later having then inserted from comboboxes in the approriate input form?

  8. #8
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    The table tbl_beneficiaries_con... (complete name is not shown) is incorrect. It should not have the handlerID or the beneficiaryID fields. And the table should not have the guardian information. The guardian is just another person so they go in the people table as a separate record. You would link the beneficiary with their guardian via the link table. Further the link should be made from personID (one) field to the handlerID (many) field of the tbl_handler_beneficiary_link. You will have to add the tbl_beneficiaries_con... table a second time and join the personID field to the beneficiaryID field of the tbl_handler_beneficiary_link table. Also, if a person has multiple phone numbers, that describes a one-to-many relationship, so the phone numbers should be records (not fields) in a related table.

    As to the municipality, province fields etc., I would have primary keys in those table and link to the main table via foreign keys. Relational database are more efficient with numerical fields rather than text fields.

    I have attached a database that illustrates the changes I describe above. Please take a look at the relationship diagram
    Attached Files Attached Files

  9. #9
    myron is offline Novice
    Windows 7 32bit Access 2003
    Join Date
    Jan 2013
    Posts
    8
    Now I realize how I misunderstood the relationship between tbl_beneficiaries_contact_dtl and tbl_handler_beneficiary_link. I got it complete the other way around! I think its clear to me now...

    I guess I have to add a field like "fkguardianID" to distinguish them too. A guardian is kind of a representative of a beneficiary (especially in case of minors or disabled persons). Not always present, but in many cases...

    I can't thank you enough for your advice and time...

  10. #10
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    I guess I have to add a field like "fkguardianID" to distinguish them too
    No, you do not need another field. The ID value of the guardian would go in the handlerID field. You would have 2 records in the link table. The first would have the ID # ( from the table that holds all people) of the beneficiary (in the beneficiaryID field) and the ID # of the handler in the handlerID field. The second record would have the ID # of the beneficiary in the beneficiaryID field and the ID # of the guardian in the handlerID field. That is why in my earlier post I suggested using generic field names: fkPPeopleID and fkSPeopleID. fkPPeople is the primary (i.e. the beneficiary) while fkSPeople is any secondary person: the handler or guardian or any other person related to the beneficiary.

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

Similar Threads

  1. Designing a small lab database
    By Johanb26 in forum Database Design
    Replies: 7
    Last Post: 11-22-2012, 08:54 AM
  2. Am I designing this database correctly?
    By Cole119 in forum Database Design
    Replies: 5
    Last Post: 07-06-2012, 01:37 PM
  3. Designing a database for a log
    By neo651 in forum Database Design
    Replies: 3
    Last Post: 10-31-2011, 11:45 AM
  4. Help with designing database
    By nimalp in forum Access
    Replies: 6
    Last Post: 09-15-2010, 10:34 AM
  5. questions on designing database
    By schultzy in forum Access
    Replies: 1
    Last Post: 07-18-2009, 03:44 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
  •  
Other Forums: Microsoft Office Forums