从应用程序向表中添加新列时的数据库设计问题

I'm currently working with a project. Now the issue arises is, I have to handle dynamic columns in database table.

我目前正在处理一个项目。现在出现的问题是，我必须处理数据库表中的动态列。

I've a table Charges, where amount of different charges corresponding to each Client will be stored. Suppose this table has the following columns-

我有一个表费用，其中将存储与每个客户相对应的不同费用金额。假设这个表有以下列 -

Client_Id     Charge_1     Charge_2     Charge_3     Charge_4

Now, the administrator can apply more new Charges for clients. In that case, new charges will be added to the Charges table as column. And this need to be handled in application run time not in database design time. This is my thought.

现在，管理员可以为客户申请更多新的费用。在这种情况下，新费用将作为列添加到费用表中。这需要在应用程序运行时处理，而不是在数据库设计时。这是我的想法。

But, it doesn't look suitable to me.

但是，它看起来不适合我。

Is there any better idea to handle this issue?? Please suggest me.

有没有更好的办法来处理这个问题？请建议我。

Thanks in advance. and I'm new with this database design.

提前致谢。我是这个数据库设计的新手。

2 个解决方案

#1

Make a Composite table, i.e. ClientCharges

创建一个复合表，即ClientCharges

You could keep your original Charges Table and your Client table and in the Client Charges table have the following columns:

您可以保留原始费用表和客户表，并在客户费用表中保留以下列：

ClientChargeId, ClientId, ChargeId

ClientChargeId，ClientId，ChargeId

In your Charges table you can keep adding (as many Charges are you require) and then reference the ChargeId in the ClientCharges table.

在您的费用表中，您可以继续添加（您需要多少费用），然后在ClientCharges表中引用ChargeId。

CREATE TABLE ClientCharges
(
    ClientChargeId          INT IDENTITY(1,1)
    , ClientId              INT 
    , ChargeId              INT
)


INSERT INTO ClientCharges
(ClientId, ChargeId)
VALUES
(1, 1),
(1,2),
(1,3),
(1,4),
(2,1),
(3,1),
(3,2),
(4,3),
(4,4)

Client 1 has Charges 1, 2, 3 and 4
客户1具有费用1,2,3和4
Client 2 has Charge 1
客户2有费用1
Client 3 has charges 1 and 2
客户3的收费标准为1和2
Client 4 has charges 3 and 4
客户4收取费用3和4

Then add foreign key constraints on the ClientId and ChargeId fields.

然后在ClientId和ChargeId字段上添加外键约束。

#2

It sounds like what you really want is a 1 to many table relationship.

听起来你真正想要的是1对多的表关系。

What you do is create a table with two columns, Client_id and charge, then you add a new row for each charge.

你要做的是创建一个包含两列Client_id和charge的表，然后为每次充电添加一个新行。

It's not clear to me how you intend to use this. Are these "charges" individual transactions? Or are they charge types associated with the client? In any event, it's bad form to modify your data model like this.

我不清楚你打算如何使用它。这些“收费”是个别交易吗？或者他们是否与客户相关的收费类型？无论如何，修改这样的数据模型是不好的形式。

When you have these kinds of recurring fields, just make them their own table.

当你有这些重复的字段时，只需将它们作为自己的表。

#1