Database design for child care

[Seridwen]

Hi,

I work for a non profit child care centre in canada. We have developed a database for our children's registration, we used access 2003. We're opening a second site and thing it's a good time to reevaluate our database.

I'm looking for suggestions on programs to use and some feedback on how to organize the fields.

currently we use each child and the main group, and other fields such as the guardians are included.
our problem is that we have multiple children from the same family, so when a phone number or address changes, we have to go through all the children and update the parent info seperately.

Does anyone have suggestions of organizing the data so that parent info can be linked across children? I hope that makes sense.

I am not the most technical but am learning, thanks!

[timothybard]

I would recommend by starting with the following tables and fields:

Parent/Guardian: Id, Last Name, First Name, etc.
Child: Id, Last Name, First Name, Date of Birth, Etc

Child-Parent/Guardian: Id, Child Id, Parent Id
Address: Id, Parent Id, Street, City, Province, Postal Code
Phone: Id, Parent Id, Phone Number

This design is not fully normalized, but to normalize it further would add another level of complication that you may not be ready for yet. This design allows the user to track multiple parents for eac child and allows the user to track multiple children for each parent. This design also allows the user to track multiple addresses and phone numbers for each parent. If a parent changes his or her name, address or phone number, this information would need to be updated in only one location.

I hope this helps!