Deciding Attributes for Entities

Now that we have our entities decided, it's time to decide attributes for each entity.

Think of each entity as a container for specific information about different parts of our system — patients, doctors, appointments, and so on. But what exactly do these entities store? That's where attributes come in!

Think of entities as tables, with attributes as columns and data as rows. Consider the following example:

We need to identify each row as a unique item, so that it can be differentiated from other items. In the Doctor table above, how does the system tell John and Mary apart?

It differentiates between rows using the Id column, which you will need for each entity. This is called the Primary Key (PK for short) in database lingo.

Next, we need to determine what type of data we store in each attribute. For Id, the type is a number (integer, or int) in the example above.

How do we decide which attributes to create?

There are three ways:

1. Go through the requirements again.
2. Use your knowledge of the system you're designing — or common sense.
3. Ask the person who helped you with requirements: meet again and discuss what information they want for each entity.

After following each of these steps, here is our full list.

Doctor

• Id — a unique number that identifies each doctor in the system.
• Name — the doctor's full name, like "Dr. Sarah Smith."
• ContactNo — the doctor's phone number for professional contact.
• Qualification — the doctor's qualifications, like "MD" or "Homeopathic Specialist," showing their expertise.

Clinic

• Id — a unique number that identifies each clinic in the system.
• Name — the name of the clinic, for example "Green Valley Clinic."
• Address — the clinic's location, including street, city, and postal code.
• WorkingDays — the days of the week the clinic is open, like "Monday to Friday."
• StartTime — the time the clinic opens each day.
• EndTime — the time the clinic closes each day.

Availability

• Id — a unique number that identifies each availability record.
• WorkingDays — the days the doctor is available, like "Monday, Wednesday, Friday."
• StartTime — the time the doctor starts seeing patients on those days.
• EndTime — the time the doctor stops seeing patients on those days.

Staff

• Id — a unique number that identifies each staff member in the system.
• Name — the full name of the staff member, like "Emily White."
• ContactNo — the staff member's phone number for internal communications.

Medicine

• Id — a unique number that identifies each type of medicine.
• Name — the name of the medicine, like "Aspirin."
• Description — details about the medicine, such as what it treats or its ingredients.

Patient

• Id — a unique number that identifies each patient in the system.
• Name — the full name of the patient, like "Jane Doe."
• DateOfBirth — the date the patient was born, used to calculate age or verify identity.
• Gender — the patient's gender.
• ContactNo — the patient's phone number for appointment reminders or contact.
• Address — the patient's home address.

Appointment

• Id — a unique number that identifies each appointment.
• Date — the date the appointment is scheduled, for example "2024-05-12."
• StartTime — the time the appointment begins.
• EndTime — the time the appointment is expected to end.
• Status — the state of the appointment, such as "Completed" or "Missed."

AppointmentNote

• Id — a unique number that identifies each set of notes for an appointment.
• Symptom — a description of the patient's symptoms, like "headache, dizziness."
• Advice — the doctor's advice for the patient, such as "rest and stay hydrated."
• TestsSuggested — any tests the doctor recommends, like "blood test" or "x-ray."
• PrivateNotes — any notes the doctor wants to keep private, visible only to them.

Prescription

• Id — a unique number that identifies each prescription.
• Dosage — how much of the medicine should be taken, like "2 tablets."
• UsageInstructions — detailed instructions, like "Take with food twice a day."
• Quantity — the amount of medicine prescribed, such as "30 tablets."

User

• Id — a unique number that identifies each user in the system.
• Username — a name or code the user logs in with, like a handle or nickname.
• Password — a secret code or key that keeps each user's account secure.
• Name — the full name of the user, for example "John Doe."
• ContactNo — the user's phone number so they can be contacted.
• Email — the user's email address, used for notifications or account recovery.

Role

• Id — a unique number that identifies each type of role in the system.
• Name — the role's name, such as "Doctor," "Patient," or "Staff," which defines what the user can do.

As you can see, we are creating a database for our system — this is the first step in any system. But a system or database design is incomplete without relationships between the different entities or tables. In the next part, we'll see what types of relationships may exist between entities and how to translate them into the language of data.