当前位置：文江博客话题详情

代码优先可以自动创建三主键关系表吗？

发布于 2024-12-17 00:44:48 字数 293 浏览 5 评论 0原文

我们以 3 个实体为例：page（网页）、link 和 area。关系如下。

页面 *----* 链接 *----* 区域

文本含义： • 一个页面有很多链接 • 一个链接可以位于多个页面中 • 一个链接可以在多个区域 • 一个区域可以包含许多链接，

这意味着，如果我没有记错的话，将需要 4 个表。 3 个实体和一个包含每个实体的主键的关系表。

有没有办法用代码优先生成这种表？

谢谢你，

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

忘东忘西忘不掉你 2024-12-24 00:44:48

首先，不。连接表根本不必有主键。它只需要外键。如果您想让它们成为主键，可以，但不一定如此。

其次，不存在 3 个主键这样的东西。主键只能有一个，但可以跨越多个列。这称为复合主键。如果您需要唯一标识每一行，则只需要主键。我通常不使用主键，或者使用合成主键（如果我在连接表中有有效负载）。

第三，不。您所描述的将使用 5 个表。页面、链接、区域、PageLink 和 LinkArea。除了通过链接之外，区域与您呈现的模型中的页面无关。

编辑：我认为您没有彻底考虑过您的数据模型。一个区域可以包含多个页面吗？或者一个区域仅在一页中？链接可以同时存在于页面和区域中吗？

编辑：根据您的评论，您想要的就是这个。

Page *---* Link *---* Area 
 *----------------------*

这需要6张表。页面、链接、区域、页面链接、页面区域、区域链接

First, no. A junction table does not have to have primary keys at all. It just needs foreign keys. If you want to make them primary keys, you can, but it does not need to be.

Second, there's no such thing as 3 primary keys. There can only be one primary key, but it may span several columns. This is called a Composite Primary Key. You would only need a primary key if you need to uniquely identify each row. I typically use either no primary key, or i use a synthetic primary key (if i have a payload in the join table).

Third, No. What you are describing would use 5 tables. Page, Link, Area, PageLink, and LinkArea. Area does not relate to Page in the model you present, other than through Link.

EDIT: I don't think you have thought through your data model thoroughly. Can an Area be in many pages? Or is an Area only in one page? Can a Link be in both pages and areas?

EDIT: based on your comments, what you want is this then.

Page *---* Link *---* Area 
 *----------------------*

This requires 6 tables. Page, Link, Area, PageLink, PageArea, AreaLink

回复收藏 0 原文

对不⑦ 2024-12-24 00:44:48

架构

是的，实际上。您可以使用统一的体系结构来实现此目的，其中每个对象在对象表中都有一个带有唯一 ID 的唯一条目。它还可以扩展，因此您可以稍后通过为每个对象类型添加一个新表来将其他对象类型添加到关系中。

除了我要描述的体系结构之外的任何其他体系结构都需要表数量呈指数增长，才能向所有现有对象类型添加额外的关系。

表 1：“对象类型”。该表将存储您拥有的对象类型的列表。它将包含三个名为“Page”、“Link”和“Area”的记录。

ID int（身份）
名称 nvarchar(50)

表 2：“对象”。该表将存储具有唯一 64 位 ID 的对象实例，该 ID 标识每个对象的类型并为该对象提供唯一的人类可读名称或描述。数据库引擎只需将 ID 字段设置为标识列即可为您处理唯一性。这是理想的，因为它可以轻松实现需要唯一对象 ID 的任何类型的系统，例如用于控制对特定页面、链接、区域等的访问的权限系统。

ID bigint Identity
类型 int（ObjectTypes.ID 表的外键）
名称 nvarchar(200)

表 3：“ObjectRelationships” 。该表表示任意类型的任意两个对象之间的关系。如果它是一个严格的层次结构，其中一个对象一次只有一个父对象，那么您实际上可以通过添加时间戳字段并创建一个为每个对象选择最新条目的视图来使其成为时间层次结构。就您而言，由于您的对象可以同时属于多个其他对象，因此不需要严格的时间层次结构；但它也足够灵活，对于某些对象，您可以为每个对象存储一个关系，并且对于对象类型的子集仍然具有完整的时间层次结构。对于双向关系，将需要两个记录来交换ObjectID 和RelatedObjectID。请注意，其他架构也需要两条记录，只是在两个单独的表中。或者，您可以简单地将单个条目视为双向关系，但这会影响您编写和解释查询的方式。

ObjectID bigint（Objects.ID 的外键）；上建立聚集索引
还建议在此字段RelatedObjectID bigint （Objects.ID 的外键）；还建议将此定向关系视为 ParentObjectID 或 ContainerObjectID，并且您仍然可以为双向关系添加反向关系
[Timestamp] datetime (SQL Server datetime2(7))

Tables 4到 2^32：“页面”、“链接”、“区域”等等。这三个或更多表，每个表存储特定对象类型特有的信息。此架构的美妙之处在于，您只需为每个新对象类型添加一个表（以及 ObjectTypes 表中的新记录），因为始终有一个表存储您的对象关系。

查询数据

如果您想查找页面中的所有链接，您可以查询页面和链接之间的直接关系，如下所示：

select L.* from Links L
inner join ObjectRelationships R on R.ObjectID = L.ID
where R.RelatedObjectID = @pageID;

或者您可以查找页面中通过区域间接相关的所有链接，如下所示：

--//view LinkRelationships = select * from Links L inner join ObjectRelationships R on R.ObjectID = L.ID
--//view AreaRelationships = select * from Areas A inner join ObjectRelationships R on R.ObjectID = A.ID

select L.* from LinkRelationships L
inner join AreaRelationships A on L.RelatedObjectID = A.ObjectID
inner join Pages P on P.ID = A.RelatedObjectID
where P.ID = @pageID;

假设您存储双向关系（每个关系需要两条记录，交换 ObjectID 和 RelateObjectID 值），您可以找到包含特定链接的所有页面。正如我所提到的，其他架构也需要两条记录，只是在两个单独的表中。

select P.* from Pages P
inner join ObjectRelationships R on R.RelatedObjectID = P.ID
where R.ObjectID = @linkID;

这种架构在存储空间方面非常灵活且非常高效（非常适合避免磁盘 I/O 瓶颈），但它要求您熟悉执行联接并将关系视为层次结构中的不同“级别”，如以下所示*示例查询中的级别视图。实际上，我创建这些视图是为了简化查询并使它们更容易理解。

Architecture

Yes, actually. You could implement this using a unified architecture where every object has a unique entry with a unique ID in an objects table. It would also be extensible so you can add additional object types into the relation later by adding a single new table for each object type.

Any other architecture other than what I'm about to describe would require exponential growth in the number of tables to add additional relations to all existing object types.

Table 1: "ObjectTypes". This table would store a list of the types of objects you have. It would have three records with names "Page", "Link", and "Area".

ID int (identity)
Name nvarchar(50)

Table 2: "Objects". This table would store object instances with unique 64-bit IDs that identify each object's type and give the object a unique human-readable name or description. The database engine handles the uniqueness for you, simply by setting the ID field as an identity column. This is ideal, because it makes it easy to implement any kind of system that would require unique object IDs, such as a permissions system for controlling access to particular pages, links, areas, etc.

ID bigint identity
Type int (foreign key to ObjectTypes.ID table)
Name nvarchar(200)

Table 3: "ObjectRelationships". This table represents relationships between any two objects of any type. If it was a strict hierarchy, where an object would have only one parent at a time, then you could actually make it a temporal hierarchy by adding a time stamp field and creating a view that selects the most recent entry for each object. In your case, since you have objects that can belong to multiple other objects at the same time, a strict temporal hierarchy is not necessary; but it's also flexible enough that you could store, for some objects, a single relation per object, and still have an intact temporal hierarchy for a subset of object types. For bi-directional relationships, two records would be required where the ObjectID and RelatedObjectID are swapped. Note that other architectures would also require two records, just in two separate tables. Alternatively, you could simply treat a single entry as a bi-directional relationship, but it would affect how you write and interpret queries.

ObjectID bigint (foreign key to Objects.ID); also would recommend a clustered index on this field
RelatedObjectID bigint (foreign key to Objects.ID); also would recommend treating this is a directional relationship as ParentObjectID or ContainerObjectID, and you can still add reverse relations for bi-directional relationships
[Timestamp] datetime (SQL Server datetime2(7))

Tables 4 through 2^32: "Pages", "Links", "Areas", etc., etc., etc. These three or more tables each store information unique to a specific object type. The wonderful thing about this architecture, is that you need add only a single table for each new object type (along with a new record in the ObjectTypes table), because there is always a single table that stores your object relationships.

Querying the Data

If you want to find all links in a page, you could query for direct relationships between pages and links like so:

select L.* from Links L
inner join ObjectRelationships R on R.ObjectID = L.ID
where R.RelatedObjectID = @pageID;

or you could find all links in a page indirectly related through areas like so:

--//view LinkRelationships = select * from Links L inner join ObjectRelationships R on R.ObjectID = L.ID
--//view AreaRelationships = select * from Areas A inner join ObjectRelationships R on R.ObjectID = A.ID

select L.* from LinkRelationships L
inner join AreaRelationships A on L.RelatedObjectID = A.ObjectID
inner join Pages P on P.ID = A.RelatedObjectID
where P.ID = @pageID;

Assuming you store bi-directional relationships (requires two records per relationship, swapping the ObjectID and RelateObjectID values), you could find all pages that contain a particular link. As I mentioned, other architectures would also require two records, just in two separate tables.

select P.* from Pages P
inner join ObjectRelationships R on R.RelatedObjectID = P.ID
where R.ObjectID = @linkID;

This architecture is very flexible and very efficient in terms of storage space (great for avoiding disk I/O bottlenecks), but it requires you to be comfortable with performing joins and thinking of relationships as different "levels" in an hierarchy as demonstrated by the *Level views in the example query. I actually create these views to simplify queries and make them more understandable.

回复收藏 0 原文

~没有更多了~