Database Questions

[eyalco]

I am a newbie and just found your forum - hope you can help me.
I built a kindergarten software but seems to have a problem : I have children registered and I keep records of the parents, other relatives etc'.
My question : what w'd be the relations between the tables since parents can have more then 1 child and so does the other relative (each parent has his own table). Should I keep all details in 1 table (personal detail for child + professional details such as positioned in adult class etc/
Hope I'm clear.
Thanks

 

[Rabbie]

I don't understand why you have a table for each parent. Surely one Parent table with many records would be best? You could then set up a one to many relationship between the Parent table and the Child table. You might find it helpful to search for "normalization" because it is very important to get the basic design correct.

 

[eyalco]

Thanks.
How do I do that if I must keep details of the father, his work place, cell phone, birth date etc, same as for the other relative etc'. I cant keep it all in one table or can I?
If I keep in the table father code and then relate it to father table, would that do?

 

[Rabbie]

It is difficult to advise you in detail without knowing what data you are storing for each child and parent. If you are storing more than 1 contact for each child then you will need a Junction table so you can set up a Many to Many relationship.
After all there may well be several contacts for a particular child and some contacts may have responsibilities for more than 1 child.

 

[Bob Miller]

I would think that a family table with FatherName, MotherName, FatherWorkPhone, MotherWorkPhone, etc. would suffice since you would have a father and mother, a single mother or a single father for a child. The only problem would be a man who fathered two children the same age with two different women, a plausible but unlikely scenario or a woman who had two children with two different fathers (not very plausible nor likely.) In those cases you would have the one parent in two records in your table.
Of course you would have a child table linked to the family table ID.

 

[eyalco]

Thanks for the reply.
So is there any problem with table being so long?
Here's the table I'm getting :
CUSTOMER_CODE
NAME
FAMILY_NAME
FATHER_NAME
FATHER_CODE
FATHER_PHONE
MOTHER_NAME
MOTHER_CODE
MOTHER_PHONE
NICK_NAME
GENDER
STATUS
BIRTH_DATE
ID_NUMBER
OWING_MONEY
ENTITLED_TO_BOOKS
ADDRESS
ZIP_CODE
PHONE
AGE
CLASS/GROUP
NAME_INCHARGE
DATE_OF_BEGINNING
PICTURE
Does that look ok?
Thanks

 

[maxmangion]

Hi,

The first thing you should consider before going any further is to normalize your data. What if for example you have 5 siblings in your school, you are going to enter the same data for father/mother details for 5 times, thus creating data redundance. Further to that what if the father/mother change their phone number ... you will have to go through all the 5 records and update the data.

Try searching this forum for normalization and this should put you in the right direction.

 

[kabz]

Thanks for the reply.
So is there any problem with table being so long?
Here's the table I'm getting :
CUSTOMER_CODE
NAME
FAMILY_NAME
FATHER_NAME
FATHER_CODE
FATHER_PHONE
MOTHER_NAME
MOTHER_CODE
MOTHER_PHONE
NICK_NAME
GENDER
STATUS
BIRTH_DATE
ID_NUMBER
OWING_MONEY
ENTITLED_TO_BOOKS
ADDRESS
ZIP_CODE
PHONE
AGE
CLASS/GROUP
NAME_INCHARGE
DATE_OF_BEGINNING
PICTURE
Does that look ok?
Thanks

There is no problem in having a long Table and by having one table makes things easier.Then all you will need is to play around with the queries when making reports.

 

[kabz]

Hi,

The first thing you should consider before going any further is to normalize your data. What if for example you have 5 siblings in your school, you are going to enter the same data for father/mother details for 5 times, thus creating data redundance. Further to that what if the father/mother change their phone number ... you will have to go through all the 5 records and update the data.

Try searching this forum for normalization and this should put you in the right direction.

I agree with max.

 

[eyalco]

Thanks for the quick reply guys.
I aldso agree with Max as the whole problem began when I tried to write 2 children for the same parents - I entered the same data again.
Can I relate only mother table to children and relate all other relatives (+ father) to the mother table?
Sorry for nagging guys...

 

[maxmangion]

Hi,

Actually, there are several ways to go about this, obviously it depends what do you want to do with the data. You may consider taking the following approach:

Child Table:

ChildID
ChildSurname
ChildName
etc.

Guardian Table:

GuardianID
GuardianSurname
GuardianName
etc

Then you should create a junction table

ChildGuardian
ChildID
GuardianID


In the above manner you are creating a many-to-many relationship where a child may have one or more guardian (father, mother, other relative) and a guardian can have more than 1 child at your school.

Just in case you are not so familiar with this approach, try searching for many-to-many relationships here on this forum, as they will give you a better description of what i just described.

Hope this helps.

 

[eyalco]

Thanks guys for your help. I'm going to work something out here.