“All Entries In Group” Database Design Aproach

问题:

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.)

回答1:

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.