need help with Church Database

[theo74]

Please help me with this Database model ... I'm I doing it right.
I'm making my first database for my church.......
-Individuals (everyone, members and workers)
-Family is one or more individual (having 1 mother, 1 father and 1 or more children)
-Address is separate because in my case 1 individual can live at many address and an address can host 1 or more individuals.
- some individuals are part of different ministries
- Contributions (donations made by members aka individuals)
- [churchrole] this field hold types of jobs or roles of individuals in the church can be member, pastor, volunteers etc

Thank you for all the help in advance

 

[MarkK]

I would expect the role in the family to be defined in your FamilyIndividual table. See how you have the family on one side and the individual on the other? What is the thing that joins the individual to the family? In that table it should be noted the role. I would not, therefore, have a tblFather, tblMother, etc... Those distinctions should not be the names of tables, those distinctions should be data IN a table.

 

[theo74]

Thank you for speedy reply...
so
1. I can remove the mother/father table and make them fields in the family table.
2. Add familyrole also in the family table..
3. Family and Individual tables are linked by familyID and IndividualID.

Another question I have is, can IndividualID(Individual table) have more than one many-to-many relationships.

Appreciate all the help

 

[Catalina]

How about a table for just individuals and a many to many relationship to a table for groups (Family, Choir, Volunteers, etc.)

So an individual can belong to many groups. Someone can belong to a family and be a member of the choir as well as being a volunteer etc.

It can go beyond father-mother-children and can include grandparents, siblings of the parents, cousins, nieces and nephews etc. A family does not always consist of just parents and children.

Does this make any sense?

 

[MarkK]

Tables might be...

tFamily
FamilyID (Primary Key)
FamilyName

tIndividual
IndividualID (PK)
FirstName
LastName
Sex
DateOfBirth

tFamilyIndividual
FamilyIndividualID (PK)
FamilyID (Foreign Key)
IndividualID (FK)
IsParent (use this with sex in tIndividual to calculate whether person is mother, father, daughter, son)

See how the role an individual plays in a family is actually stored in this third table? Also, there are a number of ways you could store that data. What I've shown here is probably the simplest since the sex of the individual obviously belongs in tIndividual. The only unknown piece of data is whether the role is Parent or Child. Mother, father, daughter, and son are actually complex data that can be calculated from the simpler data points Sex and IsParent, so you may not want to store both. Does that make sense?

 

[MarkK]

It can go beyond father-mother-children and can include grandparents, siblings of the parents, cousins, nieces and nephews etc. A family does not always consist of just parents and children.

I disagree in respect to the data. In respect to the data, a family should ONLY consist of the parent/child relationship. All the other relationships noted are simply a function of other parent/child relationships, and so all the other relationships can be calculated from combinations of parent/child relationships. This being the case, you should ONLY store the parent/child links in your data. IMO.

 

[theo74]

Pls explain the isparent field you mean an is parent yes/no or a is parent of?
Also I will have to use some code or number for family because family name is not consistent anymore some people don't have one, some use parents given name as kids last name.

 

[MarkK]

In respect to the idea of IsParent: The sex of the individual is already defined in tblIndividual, correct? So in defining that person's role in the family (by adding a record to tFamilyIndividual), the only information we don't already know is whether that person's role is parental. We already know their sex role, so the only piece of missing data to calculate the father, mother, son, or daughter role is whether their role is parental. And yes, it would be a boolean field.

We could create a field called FamilyRole that allowed us to select roles like Father, Mother, Daughter, Son, but what if we already know the individual is female? In that case it is a liability that our design still allows Father and Son as possible options. We want to design that kind of duplication out of tables so that errors like that cannot occur, not because the user knows better, but because our design doesn't allow it.

And obviously you can put whatever fields you need in the Family table if FamilyName is not appropriate.

Does that make sense?

 

[theo74]

wow Thank you. You just opened my eyes to another way of looking at my data usage. Thank you and yes it makes sense. I guess all this comes from experience. I appreciate your help.

Question: can a table have multiple relationships (many-many) to separate tables via the same primary key?

 

[MarkK]

Between two tables you can only have a one-to-many relationship. To implement many-to-many requires three tables. The design we've been discussing, having Family on one side and Individual on the other, and then the join table, FamilyIndividual, in the middle, is considered a many-to-many relationship between Family and Individual, but that many-to-many relationship is actually two one-to-many relationships.

And yes, one table can participate in multiple one-to-many relationships, for sure.

Cheers,

 

[theo74]

How can I limit the table that it users should not be able to add new record but use only current. Can I do it at the table level? e.g. tStates (lists 52 states in the usa) users should only be able to pick one, not add or edit these.

 

[MarkK]

You can't really make it completely impossible to modify data in tables, but for a table like tState, don't provide any interface that allows edits. Just populate a ComboBox with data from that table. Then, force the user to select the state from that combo.