“组中的所有条目”数据库设计方法 [英] "All Entries In Group" Database Design Aproach

问题描述

由于问题的性质，搜索类似的问题或文章是相当困难的，因为关键字有广泛的意义。因此，如果有类似的问题，请原谅。

问题。
（简体）

我有一个用户表。 表用户 >

每个用户都有自己的资产 （表资产外键对用户ID）

2用户可以是合作者。每个用户选择他想要与另一个共享的资产。 （表组FK CooperationId）（表格资产在组中）（表UserCooperation） / p>

现在是棘手的部分。

在某些情况下，用户可能想要分享

可能的方法

/ strong>（根据个人偏好排序，考虑到排队表现）。

1. 在组中）。因此，
   查询将首先检查用户是否将所有他的资产添加到组
   中，并且跳过由每个资产id而不是userId来查询。


2. strong> 2 bool在合作或组表上的标志，指示一个用户是否与其他用户共享其所有资产，而不是组。


3. 在共享表中保留 2 bool标志。但表示创建后任何资产必须包括在组中的资产表中。因此，业务逻辑将处理在该组上插入每个新资产。查询逻辑在此时不会改变

替代结构

每个用户都有他自己的组，并且加入与他的组的合作，而不是具有用于合作的共享组。 （虽然一个组没有意义，没有合作和All In Group问题保持不变。）

解决方案

考虑是定义可以是直接资产或资产组的资产类。资产组可以访问一个或多个资产。因此，用户可以与另一个用户仅分享资产或由其一些或全部资产组成的资产类别。

 创建表AssetClasses（
 ID int not null自动递增主键，
键入char（1）检查（键入（'A'，'G'）），
 ...，
约束UQ_AssetClass_IDType unique（ID，Type）
）; 
 create table Assets（
 ID int not null主键，
 ClassType char（1）检查（ClassType ='A'），
 ...，
约束FK_Assets_AssetClass外键（ID，ClassType）
引用AssetClasses（ID，Type）
）; 
 create table AssetGroups（
 ID int not null主键，
 ClassType char（1）检查（ClassType ='G'），
 ...，
约束FK_AssetGroups_AssetClass外键（ID，ClassType）
引用AssetClasses（ID，Type）
）; 
  

在AssetGroups和Assets之间会有一个多对多交集表，由许多团体和一个团体获得许多资产。在用户和AssetClasses之间还有一个交叉表，因此您可以向用户共享资产（直接到资产或通过AssetGroup间接）。

这可能看起来很复杂，它有点，但它给你你要求的一切。它很容易扩展 - 我很快就意识到，资产的所有者将必须部分资产数据。由于拥有者会同时套用到资产和资产群组，因此会进入AssetClasses表格：

  OwnerID int not null，
约束FK_AssetClass_Owner foreign key（OwnerID）
引用用户（ID）
  

任何只应用于资产或仅应用于资产组的属性都会进入相应的子表。

Due to the Nature of the Question , searching for similar questions or articles is quite difficult because keywords have a wide range of meaning. So if there is a similar question, pardon me.

The Problem. (Simplified)

I have a table of users.(Table User)

Each user Has his own assets (Table Asset Foreign key to user Id)

2 Users can be cooperators. Each user selects the assets he wants to share with the other. For use in this cooperation a Shared Group Is created containing the assets of user1 and user2 .(Table Group FK CooperationId) (Join Table Asset In group)(Table UserCooperation)

And now the tricky part.

In some occasions the user might want to Share All of his assets with a user.In order to avoid adding every time a new asset to the group manually we need a solution.

Possible Approaches. (ordered by personal preference, with queering performance in mind).

1. Having an Extra Table (All assets of user in group). So the query will first check if the user added all his assets in the group and skip querying by each asset id, but instead by userId.
2. Having 2 bool Flags on the Cooperation or Group Table indicating if one user is sharing all his assets with the other and not a group. The query will first read this and if true it will skip the group logic and will just query by user id.
3. Keep also 2 bool Flags on the sharing Table but to indicate that upon creation any asset has to be included in the Asset in Group table. So the business logic will handle inserting each new asset on that group. Query logic will not change on this occasion

Alternate Structure.

Instead of having a shared group for the cooperation, each user will have his own group and join the cooperation with his group. (Although a group has no meaning without a cooperation and the All In Group problem remains the same.)

解决方案

One scheme you might consider is to define an Asset Class which could be a direct Asset or Asset Group. An Asset Group would have access to one or more Assets. So a user can share with another user just an asset or an asset class which would consist of some or all of their assets.

create table AssetClasses(
    ID         int not null auto incrementing primary key,
    Type       char( 1 ) check( Type in( 'A', 'G' )),
    ...,
    constraint UQ_AssetClass_IDType unique( ID, Type )
);
create table Assets(
    ID         int not null primary key,
    ClassType  char( 1 ) check( ClassType = 'A' ),
    ...,
    constraint FK_Assets_AssetClass foreign key( ID, ClassType )
        references AssetClasses( ID, Type )
);
create table AssetGroups(
    ID         int not null primary key,
    ClassType  char( 1 ) check( ClassType = 'G' ),
    ...,
    constraint FK_AssetGroups_AssetClass foreign key( ID, ClassType )
        references AssetClasses( ID, Type )
);

There would be a many-to-many intersection table between AssetGroups and Assets allowing an asset to be accessible by many groups and a group to have access to many assets. There would also be an intersection table between Users and AssetClasses so you can share assets (either directly to an Asset or indirectly through an AssetGroup) to users.

This might seem convoluted, and it is a bit, but it gives you everything you asked for. And it is easily extensible -- I soon realized that owner of an asset would have to part of the asset data. As the owner would apply to both an asset and to an asset group, it would go into the AssetClasses table:

    OwnerID    int not null,
    constraint FK_AssetClass_Owner foreign key( OwnerID )
        references Users( ID )

Any attribute that applied only to an Asset or only to an Asset Group would go in the appropriate subtable.

这篇关于“组中的所有条目”数据库设计方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！