表“继承”在SQL Server中 [英] Table "Inheritance" in SQL Server
问题描述
我目前正在调查我们的联络管理数据库，我想听取人们的意见，解决一些具有共同属性的联系人类型的问题。
基本上我们有6个联系人类型，其中包括Person，Company和Position @ Company。
在当前结构中，所有这些都在地址表中有一个地址您必须存储他们的类型才能加入联系人。
一段时间以来，对联系人类型加入的一致要求会令人沮丧。
今天我偶然发现一个讨论表继承的帖子（ http://www.sqlteam.com/article/implementing-table-inheritance-in-sql-server ）。
基本上你有一个父表和一些子表（在这种情况下是每个联系人类型）。从那里你强制执行完整性，以便一个子表必须具有一个主等效的类型被定义。
我看到的方式，我不再需要将类型存储在地址中，因为所有类型的id都是唯一的。 >
我只是想知道有人对这种方法有任何感觉，无论是一个好的方法还是替代方法？
我正在使用SQL Server 05& 08应该有任何区别。
谢谢
Ed
我设计了一个数据库，就像您提供的链接一样。案例是存储许多不同技术报告的数据。报告类型数量未定义，可能会增长到大约40种不同类型。
我创建了一个主报表，具有自动增量主键。该表格包含所有常见的信息，如客户，测试站点，设备，日期等。
然后，我为每个报表类型提供一个表，其中包含与该报表类型相关的spesific信息。这个表与主人有相同的主键，并引用了主人。
我的想法是将它分成不同的表与1：1关系（通常会作为一个no-no）是避免得到一个单一的表与大量的列，这是很难维护，因为你不断添加列。
我的设计与表继承给了我分段的数据和可扩展性，而不会难以维护。我唯一需要做的是写一个特殊的保存方法来处理自动写入两个表。到目前为止，我非常高兴与设计，并没有真正发现任何缺点，除了一个更复杂的保存方法。
I am currently in the process of looking at a restructure our contact management database and I wanted to hear peoples opinions on solving the problem of a number of contact types having shared attributes.
Basically we have 6 contact types which include Person, Company and Position @ Company.
In the current structure all of these have an address however in the address table you must store their type in order to join to the contact.
This consistent requirement to join on contact type gets frustrating after a while.
Today I stumbled across a post discussing "Table Inheritance" (http://www.sqlteam.com/article/implementing-table-inheritance-in-sql-server).
Basically you have a parent table and a number of sub tables (in this case each contact type). From there you enforce integrity so that a sub table must have a master equivalent where it's type is defined.
The way I see it, by this method I would no longer need to store the type in tables like address, as the id is unique across all types.
I just wanted to know if anybody had any feelings on this method, whether it is a good way to go, or perhaps alternatives?
I'm using SQL Server 05 & 08 should that make any difference.
Thanks
Ed
I designed a database just like the link you provided suggests. The case was to store the data for many different technical reports. The number of report types is undefined and will probably grow to about 40 different types.
I created one master report table, that has an autoincrement primary key. That table contains all common information like customer, testsite, equipmentid, date etc.
Then I have one table for each report type that contains the spesific information relating to that report type. That table have the same primary key as the master and references the master as well.
My idea for splitting this into different tables with a 1:1 relation (which normally would be a no-no) was to avoid getting one single table with a huge number of columns, that gets very difficult to maintain as your constantly adding columns.
My design with table inheritance gave me segmented data and expandability without beeing difficult to maintain. The only thing I had to do was to write special a special save method to handle writing to two tables automatically. So far I'm very happy with the design and haven't really found any drawbacks, except for a little more complicated save method.
这篇关于表“继承”在SQL Server中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
