enter image description here

The system has 3 main entities:

  • Staff
  • Courses
  • Students

I will try to explain only the Student table, and I hope the rest of the data will be clear.

[ Students ]

  • student_id: Primary Key
  • full_name: *
  • phone_number: varchar, because of the (+) symbol in country code.
  • email_address: *
  • birthdate: *
  • gender: *
  • id_card_number: A student’s government issued ID card number, the number might contain letters, that’s why it’s chosen as varchar.
  • passport_number: *
  • id_card_photo_url: I also store a photo copy of a student’s ID card, and this links to that file, hosted either locally or somewhere like S3.
  • passport_photo_url: *
  • street_address: *
  • address_nearby_point: The person’s home is located nearby what? X supermarket? Or maybe Y pharmacy.
  • contact_name: The name of a friend or family member close to the person, to contact in emergencies.
  • contact_phone_number: *
  • contact_relationship: Is the chosen contact a friend? A family member? A relative?
  • educational_level: Does the person have a BSc? MSc? PhD? Nothing?
  • specialization: Is he an electrical engineer? A doctor? A nurse? A plumber?
  • medical_condition: Does he have any chronic illnesses or allergies?
  • medication: The name of the medication he uses for his medical condition.
  • bus_service: Did the student opt-in for the bus service or not (boolean)
  • is_postponed: Did the student leave one of our courses halfway through due to an emergency?
  • postpone_reason: If the above is true what was the reason? If false this would be NULL
  • course_id: A foreign key that links to the Courses table. It’s a One-To-Many relationship, because a single student can have more than one course, or a single student might finish one course and start another one.
  • payment_type: there are three types, either cash payment, payment in two installments, or three installments.
  • registration_date: This field is filled in automatically, which is the date of the form being filled in.
  • discount_amount: How many US dollars was made as a discount?
  • discount_type: Why was the discount provided? Was the student a friend or a relative of a staff member? He won a competition? etc.
  • student_attendance_id: A foreign key that links to the StudentAttendances table. It’s a One-To-Many relationship, because a single Student can have many attendances.
  • test_id: A foreign key that links to the Tests table. It’s a One-To-Many relationship, because a single student can have many tests.
  • student_payment_id: His payments. If he paid cash it will link to only one record in the StudentPayments table, if he chose to pay in three installments a record will be made for each payment he makes.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.