DynamoDB单表设计：是否应合并用户交易与转账表？

问题背景 #

我看完相关视频后，正在评估DynamoDB的单表设计方案，想请教是否应该把用户交易表和用户转账（存款、取款）表合并为一张表。二者存在关联，且访问模式都基于交易所名称和账户名。当前核心访问模式包括：

• 获取指定交易所和账户的最新用户转账记录
• 按日期范围查询所有用户交易记录
• 按日期范围查询所有用户转账记录

我的设计思路 #

我考虑用相同的PK和带前缀的SK来合并两表，比如交易记录的SK格式为TradeDate#12/17/2022，转账记录的SK格式为TransferDate#12/17/2022。同时我也清楚，这类日期范围查询需要借助GSI来实现。

后来因为需要区分转账类型（存款/取款），我把转账记录的SK格式调整为TransferType#Withdraw#TransferDate#2022-12-17 14:59:12，并修改了对应的查询代码：

修改前的代码 #

public async Task<UserTransferDto?> GetLastAsync(string exchange, string account)
{
    var queryRequest = new QueryRequest
    {
        TableName = TableName,
        KeyConditionExpression = "#pk = :pk",
        ExpressionAttributeNames = new Dictionary<string, string>
        {
            { "#pk", "PK" }
        },
        ExpressionAttributeValues = new Dictionary<string, AttributeValue>
        {
            { ":pk", new AttributeValue { S = $"Exchange#{exchange}#Account#{account}" } }
        },
        ScanIndexForward = false,
        Limit = 1
    };

    var response = await _dynamoDb.QueryAsync(queryRequest);
    if (response.Items.Count == 0)
    {
        return null;
    }

    var itemAsDocument = Document.FromAttributeMap(response.Items[0]);
    return JsonSerializer.Deserialize<UserTransferDto>(itemAsDocument.ToJson());;
}

修改后的代码 #

public async Task<UserTransferDto?> GetLastAsync(string exchange, string account, TransferType transferType)
{
    var queryRequest = new QueryRequest
    {
        TableName = TableName,
        KeyConditionExpression = "#pk = :pk and begins_with(#sk, :sk)",
        ExpressionAttributeNames = new Dictionary<string, string> { { "#pk", "PK" }, { "#sk", "SK" } },
        ExpressionAttributeValues = new Dictionary<string, AttributeValue>
        {
            { ":pk", new AttributeValue { S = $"Exchange#{exchange}#Account#{account}" } },
            { ":sk", new AttributeValue { S = $"TransferType#{transferType}" } }
        },
        ScanIndexForward = false,
        Limit = 1
    };

    var response = await _dynamoDb.QueryAsync(queryRequest);
    if (response.Items.Count == 0)
    {
        return null;
    }

    var itemAsDocument = Document.FromAttributeMap(response.Items[0]);
    return JsonSerializer.Deserialize<UserTransferDto>(itemAsDocument.ToJson());
}

内容的提问来源于stack exchange，提问作者nop