0

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.
0

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.