Implementing Table Inheritance in SQL Server
By Jeff Smith on 20 February 2008 http://www.sqlteam.com/
When designing a database, we sometimes come across situations where there are multiple types of entities that we are modeling, but we'd like them to all have certain attributes or relations in common. Using "sub-type" tables is a simple way to implement table inheritance in SQL Server.
For example, a question came up recently about modeling the following entities in a "School" database:
- Students
- Teachers
- Parents
Each of those entities has many of the same attributes, such as first name, last name, middle name, and birth date. Yet, we must separate them into multiple tables because we need to store and track different data for students, teachers and parents: students have grades and classes and parents; teachers have classes taught, skills, employment information, and so on.
In addition to sharing common attributes, these entities also have common relations. For example, for each of those entities we might also like to store addresses, phone numbers, correspondence history, etc. To do this in a nicely normalized database, we would model that data by creating additional tables:
- StudentsAddresses
- TeachersAddresses
- ParentsAddresses
- StudentPhoneNumbers
- TeacherPhoneNumbers
- ParentPhoneNumbers
- StudentCorrespondance
- TeacherCorrespondance
- ParentCorrespondance
- ...etc...
On top of the redundant, similar tables, we would need a whole mess of redundant, similar stored procedures to add/update/delete/select items from these tables. Yet, again, we need different tables for these different entities because they each have their own set of relations and attributes to track.
Is there an easier way to model this in a relational database? Absolutely -- let's take a look.
Creating A "Base Table"
We can start by recognizing that Students, Teachers, and Parents are all "People", and we can note that it makes sense to say that all People can have addresses and phone numbers and correspondence history:
- People
- PeopleAddresses
- PeoplePhoneNumbers
- PeopleCorrespondence
1/10 1 2 3 4 5 6 下一页 尾页
