Patient ID, Patient Name, Visit Date, Resources Seen During Visit (incl. Sequence Number & Name)
Example Data:
Patient ID = 1
Patient Name = 'John Smith'
Visit Date = Jan 10, 2020
Resource Sequence Number = 1, Resource Name = 'Nurse 1'
Resource Sequence Number = 2, Resource Name = 'Physiotherapist 1'
Patient ID = 2
Patient Name = 'Alex Green'
Visit Date = Jan 12, 2020
Resource Sequence Number = 1, Resource Name = 'Nurse 1'
Resource Sequence Number = 2, Resource Name = 'Nurse 2'
Resource Sequence Number = 3, Resource Name = 'Physiotherapist 1'
Resource Sequence Number = 4, Resource Name = 'Physiotherapist 2'
Resource Sequence Number = 5, Resource Name = 'Doctor 1'
Please propose a database design for this dataset
We have a dataset with 2 attributes: [Patient ID] and [Visit DateTime]
| Patient ID | Visit DateTime |
| 1 | 04 January, 2019 07:00 AM |
| 1 | 01 January, 2019 08:00 AM |
| 1 | 01 April, 2019 04:00 PM |
| 2 | 07 March, 2019 10:00 AM |
| 2 | 01 February, 2019 06:00 AM |
| 3 | 04 December, 2018 05:00 PM |
| 3 | 11 November, 2017 08:00 AM |
| 3 | 11 October, 2017 08:00 AM |
| 3 | 15 July, 2017 08:00 AM |
| 3 | 20 May, 2019 06:00 PM |
How would you query this dataset to retrieve existing attributes and two additional attributes in scope of a patient - sequential number of a visit for a patient and total number of visits for a patient?
| Patient ID | Visit DateTime | Patient's Visit number | Total Number of Patient's visits |
| 1 | 01 January, 2019 08:00 AM | 1 | 3 |
| 1 | 04 January, 2019 07:00 AM | 2 | 3 |
| 1 | 01 April, 2019 04:00 PM | 3 | 3 |
| 2 | 01 February, 2019 06:00 AM | 1 | 2 |
| 2 | 07 March, 2019 10:00 AM | 2 | 2 |
| 3 | 15 July, 2017 08:00 AM | 1 | 5 |
| 3 | 11 October, 2017 08:00 AM | 2 | 5 |
| 3 | 11 November, 2017 08:00 AM | 3 | 5 |
| 3 | 04 December, 2018 05:00 PM | 4 | 5 |
| 3 | 20 May, 2019 06:00 PM | 5 | 5 |