Designing a database for community wellfare beneficiaries

[myron]

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:



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...?

[jzwp11]

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?

[myron]

Hi there, thank you for your reply!

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.

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"

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?

[jzwp11]

"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)

[myron]

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:




I hope now it make more sense...

[jzwp11]

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?

[myron]

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:


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?

[jzwp11]

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

[myron]

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...

[jzwp11]

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.