Solved Siblings in Student Database (1 Viewer)

[ZEEq]

Hello Everyone!

I want to build a student Database for my school and i am having issue with student siblings studying in the same school how do i connect them so that they can share same address and when information required for sibling concession purpose it can be easily extracted. I can filter student by Guardian but what if they have different Guardian

School Policy is if one student has 2 siblings (second and third will get discount)

Progress so far .....

Student Table Completed
Guardian Table Completed
Junction Table StudentGuardianCompleted

Members opinion on this

 

[Gasman]

Have a Family table and a Family field in the Student table ?

 

[CJ_London]

what if they have different Guardian

what if the address is different, either because they live in different places or a small typo?

You need two tables, students and addresses. Include in the student table an FK to the PK of the address table

Also what if two unrelated students live at the same address?

So I think you need another field in your student table populated with the FK of the other sibling - or if there can be more siblings a separate table something like

tblSiblings
FKtoFirstSibling..FKtoOtherSibling
1.................................1
2..................................1
3..................................3
4.................................3
5.................................3
6.................................3

so student 1 has 1 sibling, student 2 has 3 siblings.

 

[arnelgp]

then i guess you need Another field in your table.
one field are the parent, then the other are the guardian.

when no guardian is mentioned, the parent name automatically is
written.

like in a form i fill up, it asked for my Home address and also
asked if the mailing address is the same as my home address.

 

[ZEEq]

what if the address is different, either because they live in different places or a small typo?

You need two tables, students and addresses. Include in the student table an FK to the PK of the address table

Also what if two unrelated students live at the same address?

So I think you need another field in your student table populated with the FK of the other sibling - or if there can be more siblings a separate table something like

tblSiblings
FKtoFirstSibling..FKtoOtherSibling
1.................................1
2..................................1
3..................................3
4.................................3
5.................................3
6.................................3

so student 1 has 1 sibling, student 2 has 3 siblings.

@CJ_London how do i set up this kind of table and what fields should i include

 

[ZEEq]

then i guess you need Another field in your table.
one field are the parent, then the other are the guardian.

when no guardian is mentioned, the parent name automatically is
written.

like in a form i fill up, it asked for my Home address and also
asked if the mailing address is the same as my home address.

@arnelgp which table you are referring to ?

 

[CJ_London]

i set up this kind of table and what fields should i include

Address table
addressPK
addressline1
addressline2
addressline3
....

 

[ZEEq]

Address table
addressPK
addressline1
addressline2
addressline3
....

Sorry i was asking about tblSiblings set up

 

[CJ_London]

Sorry i was asking about tblSiblings set up

as already advised

tblSiblings
FKtoFirstSibling..FKtoOtherSibling
1.................................1
2..................................1
3..................................3
4.................................3
5.................................3
6.................................3

 

[Grumm]

as already advised

tblSiblings
FKtoFirstSibling..FKtoOtherSibling
1.................................1
2..................................1
3..................................3
4.................................3
5.................................3
6.................................3

I wouldn't store all the 1---1 ones.
You cannot be a sibling of yourself.

Should look more like this :
1........... 2
2............1
3.............4
3.............5
4.............3
4.............5
5.............3
5.............4

that way, you know for each student who the siblings are.

select * from tblSiblings left join Student on FktoOtherSibling = Student.ID where FktoFirstSibling = 3

This will give you a list of all the siblings of student '3'.

 

[CJ_London]

I wouldn't store all the 1---1 ones.

it identifies the 'primary' sibling and avoids having to add two or more records for a new sibling. Student 3 with 3 siblings (so 4 students) requires

3-4
3-5
3-6
4-3
4-5
4-6
5-3
5-4
5-6
6-3
6-4
6-5

12 records my method requires just 4 records

add a 4th sibling (7) and you need to add

3-7
4-7
5-7
6-7
7-3
7-4
7-5
7-6

a further 8 records - my method just requires 1


perhaps I should made a better job of field names

StudentFK..FirstSiblingFK
1.................................1
2..................................1
3..................................3
4.................................3
5.................................3
6.................................3

equivalent query is simpler

SELECT * FROM tblSiblings WHERE FirstSiblingFK=3

or

SELECT B.StudentFK
FROM tblSiblings A INNER JOIN tblSiblings B ON A.FirstSiblingFK = B.FirstSiblingFK
WHERE A.StudentFK=3

You cannot be a sibling of yourself.

agreed but this is just a way of providing a linking value

 

[Grumm]

That makes more sense.

What about a 'Family' table ?
with ID and Name
Like Student as a field 'FamilyID'
Should normalize your table even more I think.

 

[ZEEq]

it identifies the 'primary' sibling and avoids having to add two or more records for a new sibling. Student 3 with 3 siblings (so 4 students) requires

3-4
3-5
3-6
4-3
4-5
4-6
5-3
5-4
5-6
6-3
6-4
6-5

12 records my method requires just 4 records

add a 4th sibling (7) and you need to add

3-7
4-7
5-7
6-7
7-3
7-4
7-5
7-6

a further 8 records - my method just requires 1


perhaps I should made a better job of field names

StudentFK..FirstSiblingFK
1.................................1
2..................................1
3..................................3
4.................................3
5.................................3
6.................................3

equivalent query is simpler

SELECT * FROM tblSiblings WHERE FirstSiblingFK=3

or

SELECT B.StudentFK
FROM tblSiblings A INNER JOIN tblSiblings B ON A.FirstSiblingFK = B.FirstSiblingFK
WHERE A.StudentFK=3

agreed but this is just a way of providing a linking value

thanks @CJ_London
correct me if i m wrong are you suggesting that i should make the elder student the Primary Student and link all siblings to the Primary Student?

 

[ZEEq]

@Grumm could you plz explain more about family table?

 

[ZEEq]

@Gasman also suggested family table

 

[ZEEq]

thanku to all the respected members for your time and effort

 

[CJ_London]

correct me if i m wrong are you suggesting that i should make the elder student the Primary Student and link all siblings to the Primary Student?

could be it doesn't really matter - just the first one you enter.

As suggested, Family tree would be another way

tblFamilies
FamilyPK...FamilyName
1...................A
2...................B

then add a FamilyFK field to the student table

 

[ZEEq]

could be it doesn't really matter - just the first one you enter.

As suggested, Family tree would be another way

tblFamilies
FamilyPK...FamilyName
1...................A
2...................B

then add a FamilyFK field to the student table

Looks like Family table is pretty simple solution but one more question what would be best way to describe family name we have more than 3000 students

 

[Grumm]

Looks like Family table is pretty simple solution but one more question what would be best way to describe family name we have more than 3000 students

Usually, you can put the family name in it. But make sure you have a system to handle edge cases.
For example, I have 2 names and at one point, they added me twice in class...

 

[ZEEq]

i created Family Table and now working with sample data i think this solution is simple and easy
thanks to all the members
one more question next step is Fee tables can i ask questions in same thread or should i start new one?