
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, like 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 as an example:
Doctor (Entity or Table)
Id (PK) | Name | ContactNo | Qualification |
1 | John Doe | 1234567899 | BHMS |
2 | Mary Jane | 9876543211 | MBBS, BHMS |
3 | ... | ... | ... |
Note: Each entity becomes a table in our database. Each attribute becomes a column. Data is stored as rows.
We need to identify each row as a unique item, so that it can be differentiated from other items. For example, in case of Doctor table above, how does the system differentiate between John and Mary?
The system differentiates between each row using the Id column, which you will need for each entity. This is called the Primary Key (PK in short) in the database lingo.
Next we need to determine what type of data we need to store in each attribute. For Id the type is number (integer or int) in the example above.
How do we decide which attributes should be created for each entity? There are three ways:
Go through the requirements again.
Use your knowledge of the system which you are designing or common sense.
Ask the person who helped you with requirements, meet again and discuss what information they want for each entity.
The attributes for each entity after following each of the steps listed above, here is our 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, typically shown as a single letter (e.g., "M" for male, "F" for female).
ContactNo: The patient’s phone number for appointment reminders or contact.
Address: The patient’s home address, where they live.
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: Describes 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, only visible to them.
Prescription
Id: A unique number that identifies each prescription.
Dosage: Instructions on how much of the medicine should be taken, like "2 tablets."
UsageInstructions: Detailed instructions on how to take the medicine, 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 uses to log in, 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, which can be 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.
Tip: You can use a tool like ChatGPT to find suggestions for attributes to help you start with something.
As you can see we are creating a database for our as this is the first step in any system. But system or database design is incomplete without relationships between different entities or tables. In the next part, we will see what types of relationships may exist between different entities and how to translate them to the language of data.
留言