数据库设计 - 如何设置与 2 个表中的 1 个表的 FK 关系？
在多个数据库表中维护唯一 ID 字段的最佳方法是什么？
我的数据库包含企业和人员，这两个实体都需要有一个与其关联的唯一 ID 字段。此外，还有其他表（例如地址）可以使用其 ID 作为外键来引用企业或个人。
我想到的一些想法是：
使用插入行时计算的非自动编号 ID 字段。这将解决我的唯一性问题，但是每当我想通过相关属性（例如通过地址）查找某些内容时，我都必须检查两个表，其中一个表包含我正在查找的记录。
向自动编号 ID 添加前缀以标识在哪个表中查找 ID，但是关联表中的 ID 字段可能会变成字符串或包含与它们关联的表的标志，我不确定这将如何影响性能。
将人员和企业合并到一个表中。我的问题是人员和企业具有不同的属性，需要单独的字段，这违背了我的本性，因为我更喜欢为单独的实体使用单独的表。
创建一个主表，其中包含唯一的 ID 字段、个人或企业的 ID 字段，以及一个用于说明是哪一个字段的标志。然后使用该 ID 作为我的外部参考 # 以及所有关联的表。
处理这个问题的一些更好的方法，因为我不是 dba，所以我不知道
我不知道 任何解决方案都需要能够轻松处理大量记录（要替换的数据库有几百万条记录）并且位于 MS Sql Server 上
What is the best way to maintain a unique ID field across multiple database tables?
My database contains both businesses and people, and both entities need to have a unique ID field associated with it. In addition, there are other tables (for example Addresses) which can reference either a business or a person using their ID as a foreign key.
Some ideas I am thinking of are:
Use a non-autonumber ID field that gets calculated when a row is inserted. This would solve my problem of being unique, but then anytime I wanted to lookup something by a related property (such as by an address) I'd have to check both tables for which one contains the record I'm looking for.
Add a Prefix to the AutoNumber ID to identify what table to look for the ID in, however then my ID fields in associated tables would probably either become strings or contain flags for what table they are associated with and I'm not sure how that will affect performance.
Merge the People and Businesses into a single table. My problem with this is people and businesses have different properties and need separate fields, and this kind of goes against my nature since I prefer to have separate tables for separate entities.
Create a master table containing a unique ID field, the ID field of either the Person or the Business, and a flag to say which one it is. Then use that ID as my external reference # and in all associated tables.
Some better way of handling this that I am not aware of since I am not a dba
Whatever solution I go with needs to be able to easily to handle a large number of records (the database this is going to replace has a few million records) and is on MS Sql Server
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（8）
那么你不能用这个设置来设置外键。单个外键不能引用两个可能的不同表。
我会执行以下操作之一：
您当然可以在两个实体的地址表中使用两个单独的列。 BusinessId 和 peopleid。 FK 可以有空值，所以这样就可以了。然后您可以强制执行 FK 关系，这将使您避免出现数据完整性问题。
或者设置一个包含企业和人员但字段很少的父表（只有它们真正共有的字段 - 甚至可能只有 uniqueid 和记录类型），然后您可以拥有企业、人员、地址等的子表。
或者设置单独的子表 - 业务，然后业务地址，人员和人员地址等。然后您不需要保持两个逻辑实体之间的 id 唯一。
我忘记了一种可能性，如果你有多对多的关系，你可以有地址、业务、人员，然后有一些链接表、业务地址、人员地址。
如果我有其他选择，我个人不会使用 GUID，因为它们会损害性能。
Well you can't set up foreign keys with that setup. A single foreign key can't refernce two possible different tables.
I would do one of the following:
You could of course use two separate columns inthe address table froe each of teh two entities. BusinessId and peopleid. FKs can have nulls, so this would be ok. And you can then enforce the FK relationship which will keep you from having data integrity problems.
Or set up a parent table that includes both businesses and people but has very few fields (only those they really have in common - even maybe only a uniqueid and a recordtype) then you can have child tables for business, people, address, etc.
or set up individual child tables - business and then business address, people and people address, etc. Then you don't need to keep the ids unique between the two logical entities.
I forgot one possibility, if you have many to many relationships, you could have Address, Business, People and then some linking tables, BusinessAddress, PeopleAddress.
I personally would not use GUIDs if I had a another choice as they can harm performance.
你也可以转变你的想法。
个人或企业拥有地址 ID，而不是让地址拥有个人或企业 ID。
在我的书中，这是一种更自然的思考方式……一个人有一个地址。
You could also invert your thinking.
Instead of having Address have the person or business id, the person or business has the address id.
This is a more natural way of thinking about it in my book... a Person HAS an Address.
这就是 GUID 的用途。
This is what GUIDs are for.
有几种不同的模式可以执行此操作，但最简单且最灵活的一种是使用唯一标识符 (GUID)；大多数数据库都有一些构建这些的工具（例如，SQL Server 是 NEWID()）。它们比其他身份证表格更大，但它们可以完成您正在寻找的工作。
There's a few different patterns for doing this, but the easiest one and most flexible is to use unique identifiers (GUIDs); most DBs have some facility for building these (SQL Server is NEWID() for example). They're bigger than other ID forms, but they'll do the job you're looking for.
某些数据库允许您使用带有空值的外键。有些不支持，我不记得 SQL Server 是否支持。如果您的地址允许，您可以在地址表中包含 2 个 ID 列，一列指向人员，一列指向企业。这种方法也有优点和缺点。缺点之一是您的 DBA 可能会不赞成它，但如果您的数据库允许，那么它可能是替代方案之一。
Some databases allow you to have foreign keys with null values. Some do not, and I cannot recall if SQL Server does. If yours allows it, you can have 2 ID columns in the address table, one that points to People and one that points to Businesses. That approach also has pros and cons; one of the cons being that it is probably frowned upon by your DBA, but if your database allows it, then perhaps it could be one of the alternatives.
创建一个“超类型”表来标识企业和人员，并使用外键引用该表。这是这种情况的常见模式。请参阅：参与方数据模型。
Create a "supertype" table that identifies both businesses and people and reference that table with your foreign key. This is a common pattern for the situation. See: Party Data Model.
GUID 可以作为唯一标识符，仅此而已。
GUID 的问题在于它们的大小，尤其是当您的数据库很大时。
它们不应该用于连接（索引、外键……）。
我们采用了这种精确的数据库设计，当记录数量变得太大时，我们必须将其改回整数。
我还想指出，您需要谨慎对待您的个人/企业/地址的设计。这是多对多的关系。一个企业/个人可以有超过 1 个地址，一个地址可以用于多个企业/个人...
如果您想将企业和个人分开，您可以有 2 个表 PersonAddress 和 BusinessAddress 来保存关系，并且在查找两者的地址时必须进行并集，或者您可以为两个企业使用一个表 EntityAddress一个 Persons 和一个 EntityNature 字段一起告诉它是一个企业还是一个人。
GUIDs are good as unique identifiers, nothing else.
The problem with GUIDs is their size, especially if your DB is going to be huge.
They should not be used in joins (Index, Foreign key,...).
We had this exact DB design that we had to change back to Integers when the number of records became too large.
I would also point out that you need to be careful with your design for your persons/businesses/addresses. It's a many to many relation. A business/person can have more than 1 address, an address can be for multiple businesses/persons...
If you want to keep businesses and persons separate, you can have 2 tables PersonAddress and BusinessAddress to hold the relations and you would have to do a union when looking up an address for both, or you could have a single table EntityAddress for both Businesses an Persons together with an EntityNature field telling if it's a business or a person.
企业和人是真正、完全、独立于企业的吗？
他们没有任何共同点，甚至连“他们出生的那一天”都没有？
商家不把他们俩当成“交易对手”吗？
我想说明的是，如果你足够仔细地观察业务，而不是像普通 IT（所谓的）“专业人士”通常那样戴着通常令人眼花缭乱的眼镜，那么你会很快发现你正在寻找的共性。为了。
在数据库中定义一个表来记录这些共性（即使它只不过是标识），并使地址引用该表。
Businessess and people are truly, completely, separate to the business ?
They have nothing what so ever in common, even not "the day they were born" ?
The business does not treat the both of them as "counterparties" ?
What I'm trying to illustrate is that you look hard enough at the business, without the usually blindening glasses that the average IT (so-called) "professional" usually looks through, then you will very rapidly find the commonalities that you are looking for.
Define a table in your database to record those commonalities (EVEN if it is nothing more than the identification), and make the addresses reference THAT table.