Implementing Table Inheritance in SQL ServerBy Jeff Smith on 20 February 2008 http://www.sqlteam.com/When designing a database, we sometimes come acr......
from Students where PersonID=1
-- change the type from a student to to a teacher
update People set PersonTypeID=2 where PersonID = 1
-- add teacher attributes:
insert into Teachers (PersonID, HireDate)
values (1,'2007-01-01')
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
And there you have it! A "one-to-either" constraint, where each row in one table has a related row in only one of several possible tables, without the need for any triggers or any complicated T-SQL programming to maintain data integrity.
Conclusion
Once things are in place, we have a simpler data model with fewer tables and stored procedures, and we are guaranteed consistency when dealing with common attributes and relations of "People" in the database. We can easily create new types of People simply by adding a row to the PersonTypes table and then creating a new "sub-table". As soon as the new sub-table is created, that new type immediately has all of the attributes and relations of a Person – without coding up anything or making any other changes to the database!
10/10 首页 上一页 8 9 10 |
