Key: This is the main key of your table. It's like a unique ID for each row in your database table. Just as no two people have the same social security number, no two rows can have the same primary key. It ensures that each entry in your table is unique and can be found quickly
Example of using Key Fields in your database table:
Let’s say you have a sign-in sheet at two different hospitals: Hospital A and Hospital B. Each patient signs in with their patient ID, and we record which hospital they visited. So, Patient 01 can have records Hospital A and Hospital B but cannot have more than one record at each Hospital, like the table below:
PatientID | HospitalID | Reason |
|---|---|---|
01 | A | Check-up |
02 | A | Annual Physical |
01 | B | Follow-up |
02 | B | Emergency |
Without the addition of a third key field, the example above does not make a lot of sense, why can’t Patient 01 visit Hospital A again? This is an example of having 2 key fields in your database table. There can’t be a duplicate of Patient 01 at Hospital A as key fields do not allow that.
To allow for a more than one visit per patient, a third key field needs to be set up. Let’s call it VisitDate:
PatientID | HospitalID | VisitDate | Reason |
|---|---|---|---|
01 | A | Nov-01-2023 | Check-up |
02 | A | Nov-01-2023 | Annual Physical |
01 | B | Nov-01-2023 | Follow-up |
01 | A | Nov-08-2023 | Consultation |
02 | B | Nov-08-2023 | Emergency |
Notice, Patient 01 visits Hospital A twice which, before we added the third key field, would not be possible. Because the VisitDate is a different value, it makes the entire entry unique due to VisitDate being a key field.
In this table, the addition of the third key field VisitDate allows for multiple visits by any Patient as the date of the visit will make each entry unique.
This is how the table would look in IguanaX:
