Database structure on school app

Need help Database
1

Hi I’m building an app for schools . and the app design to many user to sign up

like a Student and parents and staff and school it self

so should I create a field of every information I need " let’s say first name " on the user type , or I just create a type for each student and staff and parent and school and then create a field " first name "?

in second case the user type will be almost empty

2

I really don’t know what you want to do with your app, but FWIW, I would suggest the following:

If all of the people (students parents, staff) are going to be users, you can just add these fields to your User table. Otherwise, you’ll need a different table, potentially named “Members”.

Field/datatype
name/text
studentYesNo/yesno
parentYesNo/yesno
staffYesNo/yesno
schoolID/text

You’ll likely need a table for the schools, whose unique ID you would use to fill in the schoolID above.

The reason I would add all of these yes/no datatypes to your table is because it’s entire possible that:

  • a parent could also work at the school
  • a student could work at the school
  • a parent (who works at the school and is also a student at the school) also has a kid who’s a student at the school and who also works at the school.

Now…to make matters even more complicated, you state that the app is for schools, not just a single school.

If THAT is the case you will need a cross-reference table. Let’s call it SchoolMembers with the data fields:
schoolID/text
memberID/text

And you would just populate it with the necessary unique IDs from your Members entry and Schools entry. This way you’d be able to have a single person be associated to multiple schools, and a single school associated with multiple people. You’d also be able to cover situations such as a person is a parent for one school but is a staff member at a different school (or any of a myriad of combinations).

3

wow wow I really can’t thank you enough

But one last question , what about classes , I want school to be able to create class and add speceific students and staff for it , what is the best way to setup the room data

4

First table: SchoolClasses

schoolID/text
nameOfClass/text
memberID/text

You’d have one row for each class the school has, and the memberID would be populated with the uniqueID of the staff member running the class.

Second table: SchoolStudents
schoolClassID/text
memberID/text

So you’d have multiple rows for a single class, with an individual row for each student in the class.

5

wonderful

Last question

i want to link parent with their own kid , the process is when parents sign up with the same email or phone number that the school put on the student profile
they link together automatically

1- School create student with email or phone number
2- Parent sign up with the same email that on student profile
3- connect to their kid

I know it looks a basic question but I always looking for best data structure for performance