Chapter 12 Exercise 15

15. Assuming the company has operated for five years, how would you partition the data to reduce storage needs and improve performance?

Keep in mind that many patients do not show up on a regular basis, so five years is not that long of a time frame, and patients will expect physicians to have access to the older data. But, the simplest partition is to move older data from Visit, PatientDiagnoses, and VisitProcedures onto slower and/or optical drives. Particularly with Blu-Ray available, it will make sense to move as much older data as possible to the slower drives. Or, at least pull it off the RAID drives onto cheaper, slower drives since it will not need to be updated. So, partition those three tables based on either VisitID or VisitDate. You should be able to write a partition function that converts date ranges into VisitID ranges, but VisitID by itself should be accurate enough.