Assignment:
Preparing an Adequate Data Model" Please respond to the following:
• Using specialization hierarchies can provide an efficient application development structure. Justify the use of surrogate primary keys for a database design. Provide an example to support your response.
• Historical data can be important to measuring performance. Prepare an example on how time-variant data can be properly captured and maintained within a database system.
Classmates:
Using specialization hierarchies can provide an efficient application development structure. Justify the use of surrogate primary keys for a database design. Provide an example to support your response.
Depending on the application and information being stored within a database a surrogate primary key could make sense for a reservation type system where a dedicated primary key may not be necessary. An instance of this would be a hotel reservation system. The database could create a primary key from the reservation holder's last name and date of check-in. This would allow the system to have several searchable fields and also a database generated key the customer could use to reference their reservation.
A surrogate key can also be cumbersome since data storage is starting to become cheaper so having one additional dedicated field to auto-generate a primary key makes more sense. However, this may not work in some of the larger database systems where you could have billions of transactions versus a smaller database where thousands of transactions occur. According to Kantor, the best time to use a surrogate key is when the key has no contextual or business meaning. It is manufactured "artificially" and only for the purposes of data analysis (Kantor, 2016).
Historical data can be important to measuring performance. Prepare an example on how time-variant data can be properly captured and maintained within a database system.
Aside from the examples provided in the text, one of the best time-variant data sets to store would be an employee evaluation tracker. You could have a table for the data of the employee's most current evaluation and a historical table showing the employee's last evaluations with relevant data. This could be used to analyze trends in employee performance or to see how different managers evaluate different employees within the company. This data analysis could be used to ensure evaluations are fair and balanced and managers are not using the information to negatively affect employees.
References:
Kantor, H. (2016, July 11). When (and How) to Use Surrogate Keys. Sisense. Retrieved from https://www.sisense.com/blog/when-and-how-to-use-surrogate-keys/