需要为我的数据库模式设计更好的设计，在表中创建特定行，列的更改请求值

I have a case where I would like to make a change request for a specific field on a table, where there will be two parties one that makes the change request of specific field and the other one that accepts it.

我有一个案例，我想对表上的特定字段进行更改请求，其中将有两个方提出特定字段的更改请求，另一方接受它。

Let's assume for now I have this table called 'Deals' with current fields as:

我们现在假设这个表名为'Deals'，当前字段为：

deals: investor_id, team_invested_id , investment_size, investment_type , pmv, etc...

Now both can make request for a change for any of the attributes on table and the request has to be approved by the each other.

现在，两者都可以请求更改表上的任何属性，并且请求必须由彼此批准。

My solution so far is this, but I do doubt on this: Make new table 'deals_change_requests' with fields as:

到目前为止，我的解决方案是这样，但我对此表示怀疑：使用以下字段创建新表'deals_change_requests'：

deals_change_requests: id, deal_id, team_requested_id, column_name, new_value, approved.

This might work well, but it forces me to treat all the attributes of deals the same type format... cz I should decide only one type for new_value attribute and that should be VARCHAR so far I have seen...

这可能效果很好，但它迫使我将交易的所有属性都视为相同的类型格式... cz我应该只为new_value属性决定一种类型，到目前为止我应该看到VARCHAR ...

Any better solution, idea?

有什么更好的解决方案吗？

1 个解决方案

#1

I'd suggest baking this into the main table:

我建议将它烘焙到主表中：

investor_id, team_invested_id , investment_size, investment_type , pmv, version, approved, team_requested_id
          1,                1,             1000,             BOND,  11,       0,        1,                 1
          1,                1,             1000,             BOND,  13,       1,        0,                 2

In the example above, there is an investment created by team 1, with a PMV of 11, and it's approved. There's also a change request from team 2, changing the PMV to 13, but it's not yet approved.

在上面的示例中，团队1创建了一项投资，PMV为11，并且已获得批准。还有团队2的变更请求，将PMV更改为13，但尚未批准。

This means you don't have to mess around with data types, and all the change history lives in the same table. You can obviously include dates to show when the changes became effective.

这意味着您不必乱用数据类型，并且所有更改历史记录都存在于同一个表中。显然，您可以在日期中显示更改生效的日期。

#1